3

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?

Michael
  • 674
  • 1
  • 5
  • 13
  • See: http://stackoverflow.com/questions/279170/utf-8-all-the-way-through. Also, you might want to restart your mysql server and web server, then check the tables again. Sometimes the "right" values get cached even though the "wrong" values are inserted in the database. – Seth Aug 12 '10 at 04:58
  • Compare the hex dumps (`SELECT HEX(column) FROM table`) and see if the actual data is the same. – NullUserException Aug 12 '10 at 05:02

2 Answers2

3

  1. Try to save all files in UTF-8 without BOM.

    Refer to byte order mark (BOM)

  2. Do with MySQL next operations:

    SET NAMES UTF-8
SET CHARACTER SET UTF-8

    Refer to 10.1.5. Configuring the Character Set and Collation for Applications

  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>

    Refer to Setting charset in htaccess

Zuul
  • 16,217
  • 6
  • 61
  • 88
Alex Pliutau
  • 21,392
  • 27
  • 113
  • 143
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.

thomasrutter
  • 114,488
  • 30
  • 148
  • 167
  • Given the description, I suspect that "table one" has the wrong character encoding set, while "table two" is set correctly. – Frank Farmer Aug 12 '10 at 05:24
  • You may be right - want to add that as an alternative answer? – thomasrutter Aug 12 '10 at 05:26
  • My connection is encoded in utf8 and both tables and the fields in question are all set to utf8. – Michael Aug 12 '10 at 05:39
  • Adding array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") to the PDO connection did the trick. It's still a mystery why one table took the Chinese character and the other not! Thanks for the help! – Michael Aug 12 '10 at 06:59