fplanque: dev blog

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! ;)