Why is PHP/MySQL inserting my Chinese characters differently?

Question

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?

Answer 1

1) Try to save all files in UTF-8 without BOM. 2) Do with MySQL next operations:

SET NAMES UTF-8
SET CHARACTER SET UTF-8

3) In root folder create file .htaccess with next content:

AddDefaultCharset utf-8
AddCharset utf-8 *
<IfModule mod_charset.c>
CharsetSourceEnc utf-8
CharsetDefault utf-8
</IfModule>

Answer 2

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.