2

I have Arabic text stored in mysql like this format 

شقق جديده غبر مسكونه 220 متر..
.150متر..طبربور ..ااسكانات قموم 
والنجار للاستÙسار

it is in this format because I didn't use the below before inserting data into database from HTML textboxes:

mysql_query('SET CHARACTER SET utf8');

My question now, how to convert the text into MySQL columns to readable text like this:

شقق جديده غبر مسكونه 220 متر ..150متر..طبربور ..ااسكانات قموم والنجار للاستفسار

I tried the below for one column cell to check, but it didn't work:

mysql_query('SET CHARACTER SET utf8');

$sql  = 'SELECT content FROM messages WHERE id=500';
$qry = mysql_query($sql);
$result =mysql_fetch_object($qry);

$text= $result->content;

mysql_query('SET CHARACTER SET utf8');

$sql  ='UPDATE messages SET content= "'.$text.'" where id=500';
mysql_query($sql);

Note: if I use the below

mysql_query('SET CHARACTER SET utf8');

before I insert data into database from HTML textarea, then it works fine for store and read, but I need to convert the already entered data into tables.

Thanks.

The solution in brief can be done in 3 simple steps:

A. Change the Database collation to utf8 via the command below:

ALTER DATABASE <db_name> CHARACTER SET utf8 COLLATE utf8_general_ci;

already the table cells I want to change have collation of utf8_general_ci

B. Implement this query to change the content:

update messages set content=CONVERT(BINARY CONVERT(content USING latin1) USING utf8);

C. add the below before you connect to the DB:

mysql_query('SET CHARACTER SET utf8');

And you are done!!! Thanks for all

moderns
  • 650
  • 10
  • 23

1 Answers1

1

Use the ALTER DATABASE and ALTER TABLE commands.

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Source.

Community
  • 1
  • 1
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • How it did not work? What did you run, how did it fail, what was the error message – Lajos Arpad May 22 '15 at 15:07
  • 2
    That's not enough. You didn't provide any change of content in table cells, you should implement this query: update messages set content=CONVERT(BINARY CONVERT(content USING latin1) USING utf8); – moderns May 22 '15 at 18:32