Thursday, August 1, 2013

php page cannot display Chinese character from mysql database

Today, I setup a mysql database with collation : utf8_general_ci. I input Chinese character from web page with meta tag : charset=utf-8".  When I retrieved these records, they were shown as "???".  After studying, I found that I need the following line :

mysql_query('SET character_set_results=utf8'); 


before  

$result = mysql_query($query);

I hope this information is useful for those who are developing web page with Chinese characters records in mysql.

php Special Character Escape Sequences

Escape SequenceCharacter Represented by Sequence
\0 An ASCII NUL (0x00) character.
\' A single quote (') character.
\" A double quote (") character.
\b A backspace character.
\n A newline (linefeed) character. 開新行
\r A carriage return character.
\t A tab character.
\Z ASCII 26 (Control+Z). See note following the table.
\\ A backslash (\) character.
\% A % character. See note following the table.
\_ A _ character. See note following the table.

useful examples
==================

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT 'This\nIs\nFour\nLines';
+--------------------+
| This
Is
Four
Lines |
+--------------------+

mysql> SELECT 'disappearing\ backslash';
+------------------------+
| disappearing backslash |
+------------------------+