MySQL uft8_unicode_ci

Question

Hi,

I want to make sure something is write.

My database tables are utf8_unicode_ci and my site encoding and header is utf-8 etc and so on.

I done a test and in my guestbook i entered this:

á
ʵßăāÇϢϞﻨ☺ 

▓ ▓ẻ ▓ẻṎ

۞ ݤ

Now great it displays on the webpage like it should and i tested other languages to etc, but on checking this in phpmyadmin it is showing in the text field value area in phpmyadmin as:

á
ʵßăÄÇϢϞﻨ☺ 

▓ ▓ẻ ▓ẻṎ

۞ ݤ

Can someone tell me why this is showing like this in phpmyadmin? i mean is it stored like that and it that normal or should it be stored exactly as i typed it? It is happening on all foreign language like chineese etc aswell, basically all foreign languages.

I checked to ensure and in phpmyadmin it says:

MySQL connection collation: utf8_unicode_ci and MySQL charset: UTF-8 Unicode (utf8)

Maybe it's phpmyadmin? if so what must i do to get phpmyadmin to display utf-8 correctly, either that or mysql is not storing it rite although it displays fine when outputting to webpage.

Thanks for any help, btw must say this is a great site, very quick support and very nice helpful people on here, so thanks to everyone who has already helped me since joining earlier on today! :)

Thank you!

Now

Answer 1

No, it is not PhpMyAdmin. It has always been using UTF-8 all the time.

The DB connection in your PHP application itself is likely not using UTF-8. Although the bytes which are transferred from one to the other side are the same, they are been encoded and decoded using the wrong charset, namely the platform default one which is most likely ISO-8859-1 as per the patterns of the Mojibake characters (first character of the UTF-8 pair/triplet becomes then often Ã, Â, Ä and like).

You need to let PHP inform MySQL using mysql_set_charset() that you're working with UTF-8 data:

mysql_set_charset("utf8");

Note that this only works in PHP >= 5.2.3 and MySQL >= 5.0.7, else you need to do a SET NAMES query instead, see also the aforelinked PHP manual. Also note that you need to truncate and refill the DB with new data, you can't redisplay it on the webpage anymore without converting it back using some SQL script.