Wednesday, September 28, 2011

MySQL data contains chinese charactor become äöüßæåéé

It took me hours figuring this.
All my mysql data became symbols.
Then finally, i found the culprit.
1st, i converted my field and default charset of my table to unicode.
But i did not update the existing data to unicode. So it was my mistake.

2nd, in php, i changed my mysql_set_charset("UTF-8");
So now all the data from the table which contains original latin will be forced to utf-8.
So the symbol appear in my html output, so does phpmyadmin.

To fix this, i wrote a program to retrieve the data from latin charset, and
then output it as a code in array to replace the data later back to the database as utf-8.

mysql_set_charset("latin1", $conndb);
mysql_query("select ...");
$aData = mysql_fetch_assoc($query);

mysql_set_charset("utf-8", $conndb);
$aData["fieldname"] = utf8_encode($aData["fieldname"]);
//then update those record with newly encoded utf8
mysql_query("update table ... ");

so, the next time we query the database, ensure to use
mysql_set_charset("utf-8", $conndb);

No comments: