We had this quite interesting situation at work today: I export an UTF-8 MySQL database into an UTF_8 SQL file. I binary FTP the file over to him. He plays the SQL in PHPmyAdmin all configured for UTF-8. He then checks the data in PHPmyAdmin: special chars display right under an UTF-8 page encoding. He checks under the command line: the DB contents seem to be UTF-8 encoded too…
Now he goes to our app, pulls out some data… and gets a special chars mess… although the html page is displayed as UTF-8. If we force the display to Latin-1, the special chars display correctly again.
The reason for this is that there is actuallly a charset translation taking place between the mysql client (which is PHP here) and the mysql server.
We found we could fix this by editing my.cnf and specifying the following:
# The default character set that will be used when a new schema or table is
# created and no character set is defined
default-character-set=utf8…instead of the latin-1 he had there for some obscure reason.
However, there must be a more explicit way to have PHP retrieve the mysql data as UTF-8, even though we could not find that in mysql_connect() or somethig alike.
Guess, we’ll have to investigate the PhpMyAdmin code and see how those guys do it. Btw, this reminds me of a conference by Rasmus Lerdorf where he said the code for PhpMyAdmin was pretty clean and should be studied. Yep, we definitely gotta do that! ;)
Comments from long ago:
Comment from: Julien Moorrees
I had the same issue in b2 evolution. The administrator page showed all the accents etc correctly, but the log didn’t show it correctly.
You can fix this by editing the php.ini and adding / setting: default_charset = “iso-8859-1” or in your case to utf 8.
In that case you don’t have to change the settings for mysql.
2005-01-13 23-21
Comment from: Haprog
To set the encoding in which MySQL returns the data to the SQL client (in this case PHP), you need to run the following simple SQL query after connecting to the database: “SET NAMES ‘utf8’”
2006-11-28 13-39
Comment from: Nick
Thanks, this really helped me displaying some Korean text. This programming gotcha didn’t get me.
2007-01-07 14-19
Comment from: Damián
Thanks this help me too. i have to deal with ñ char ¡bendito idioma español!
2007-02-11 14-34