Chinese characters in database prepared statement

Question

I might have a simple problem of encoding but i can't figure it out. I have addresses that can be in English or in Chinese in a MySQL database, so i used utf8_unicode_ci . I don't have problems retrieving my chinese characters from the database, but I can't use the chinese characters in a prepared request.

I explain: If I type

$bdd= new PDO('mysql:host=localhost:3306; dbname=****;charset=utf8', 'root', '');   
$list_business = $bdd->query('SELECT * FROM business WHERE address LIKE N\'台灣台南市\' ');
$nb_business=$list_business->rowCount();

I will get one result, because one of the addresses contains "台灣台南市" But if I try to use a prepared request:

$list_business = $bdd->prepare('SELECT * FROM business WHERE address LIKE ? ');
$list_business->execute(array('%'.$_POST['address'].'%'));
$nb_business=$list_business->rowCount();

If $_POST['address'] is in English it works, in Chinese it doesn't :p

EDIT :

If i echo $_POST['address'] it shows the address in chinese that I input so that part is okay, although, if I echo the address from database it will look like this : "701\u53f0\u7063\u53f0\u5357\u5e02\u6771\u5340\u88d5\u8c50\u885775\u865f".

EDIT2:

When asking for show variables like 'char%'; I got this result

character_set_client utf8mb4

character_set_connection utf8mb4

character_set_database latin1

character_set_filesystem binary

character_set_results utf8mb4

character_set_server latin1

character_set_system utf8

character_sets_dir c:\wamp\bin\mysql\mysql5.6.17\share\charsets\

Please, help!

Thanks beforehand,

Q

Have a look at this: stackoverflow.com/questions/3513773/… — Vedant Terkar
Could you try this instead ~ prepare("SELECT * FROM business WHERE address LIKE CONCAT('%', ?, '%')") and execute([$_POST['address']]) — Phil

Jay Zhou Jay Zhou · Accepted Answer · 2015-10-27T03:32:28

have you set your language environment to "UTF-8"? have you set your mysql character set to utf-8? in mysql ,exec "show variables like '%char%'; it should return

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Chinese characters in database prepared statement

3 Answers