Why does PHP / MySQL insert my Chinese characters differently?


Gday All,

I have a baffling problem whilst trying to insert some chinese characters into my MySQL database from PHP using mysqlnd.

I have a form that accepts some details, eg Internal Name, External Name, Shot Name, etc...

I enter "语言测试" (Language Testing) into all three fields in the form.

I am submitting my information using an inner join eg:

UPDATE table1 INNER JOIN table2(table1.name = "value1", table2.ext_name = "value2", table2.ext_name = "value3")

Where both tables and the fields in question are set to utf8_general_ci (I have also tried utf8_bin)

The the insert works correctly however I am seeing two values inserted into the database.

In table one I see "语言测试" and in table two I see "语言测试".

What could be causing my insert of exactly the same data from the same php form to show up differently in two separate MySQL database tables?

In MySQL you not only have to set the character encoding of the table (or its columns) but you have to set the character encoding of your connection between PHP and the database, which is done each time you connect.

In PHP you can use mysql_set_charset(), or if you're using PDO include charset=UTF-8 in your data source name.

This takes the place of (and PHP recommends it in favour of) SET NAMES in MySQL. In older versions of PHP, you used the MySQL command SET NAMES UTF8 each time you connected instead.