mysql_query returns the wrong character encoding for foreign characters

0
votes

My database table is UTF-8 however when querying it through PHP the characters come out different.

When viewing the data in the database it appears like this:

汕头å›åŽå¤§é…’店

but in PHP it comes out as 

汕头åÂÂââ

I have noticed that when I encode the data with base64_encode before inserting into mysql then decode it when it comes out then it shows correctly in the PHP and browser and shows as it does in the database: 

 汕头å›åŽå¤§é…’店

But there is data where I can't encode it first so I can't always do this so I'm wondering why mysql_query makes it come out differently. I've tried many things

  1. Doing before the query:

    mysql_query ("set character_set_results='utf8'");

and

mysql_query("SET NAMES UTF8");

  1. Doing this before the query:

    mb_internal_encoding('UTF-8'); mb_http_output('UTF-8'); mb_http_input('UTF-8');

  2. This is my html page header

    "meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /"

  3. using utf8_decode on the field in question but seems to make it worse

But it makes no different to the output I can see both the correct (the base64_decode ) and incorrect characters in the same page

thanks!

1
phpmysqlcharacter-encoding
Have you tried also indicating that the charset is utf-8 in your HTML? – Michael Aaron Safyan
yes I got that in there, it doesn't make a different I can see both the problem one and the one that works on the same page – user2760338
Try adding this in your php code at the top of the page header("Content-Type: application/html; charset=utf-8"); – John
yep have tried that too, the problem is not the output to the browser really, it's in the php before it reaches the browser mysql_query is returning the wrong charset unless it's encoded and decoded with base64 – user2760338

1 Answers

-1
votes

ok worked it out, I believe Cake 1.2 was trying doing a conversion of some sorts on the data or telling mysql to somewhere.

After removing encoding=>'utf8' from the database.php in Cake it works fine, no need to set UTF8 anywhere in the PHP code or MYSQL when the MYSQL table is UTF8!

Also another problem was that I think older data was stored incorrectly in the database, perhaps the table was not UTF8 before so when PHP read the data from the CSV file it stored it as garbled (incorrect) data