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: http://stackoverflow.com/questions/3513773/change-mysql-default-character-set-to-utf-8-in-my-cnf — Vedant Terkar, Oct 27 '15 at 03:42
Could you try this instead ~ `prepare("SELECT * FROM business WHERE address LIKE CONCAT('%', ?, '%')")` and `execute([$_POST['address']])` — Phil, Oct 27 '15 at 03:43

Jay Zhou · Answer 1 · 2015-10-27T03:57:46.680

1

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/ |
+--------------------------+----------------------------+

edited Oct 27 '15 at 03:57

answered Oct 27 '15 at 03:32

Jay Zhou

51
6

Can you elaborate please? =) – Kunting Oct 27 '15 at 03:33
like this mb_internal_encoding('UTF-8'); – Jay Zhou Oct 27 '15 at 03:36
nop, still not working =/ Please see the Edit in my question – Kunting Oct 27 '15 at 03:49
have you check your mysql character set? like the table I edited :) – Jay Zhou Oct 27 '15 at 04:03
so maybe you can try to set character_set_server = UTF8 and character_set_database =UTF8,and reinitial your database. – Jay Zhou Oct 27 '15 at 05:29
If i change it directly in mysql nothing happens. So i looked for my.cnf, and turned out i only have my.ini In my.ini, i don't see any line refeering to character_set_*** Should i add lines? – Kunting Oct 27 '15 at 06:10
after add this two lines ,you should restart your mysql .and reinitial your database. – Jay Zhou Oct 27 '15 at 06:13
It had to be pasted in two different sections, now i've got my variables all set up for utf8 and i restarted mysql (i can check that all variables are utf8). However, i still have my initial problem. How do i reinitialize? – Kunting Oct 27 '15 at 06:27
delete your old database ,and recreate it and insert the data with utf8 – Jay Zhou Oct 27 '15 at 06:41
and have you try PDO::exec("SET NAMES 'utf8';"); before exec sql statement ? – Jay Zhou Oct 27 '15 at 06:52
so sorry to hear that :( – Jay Zhou Oct 27 '15 at 07:05

score 0 · Answer 2 · answered Oct 27 '15 at 03:03

0

try setting the character set of the page itself (if you haven't already).

header('Content-Type: text/html; charset=utf-8');

answered Oct 27 '15 at 03:03

myte

887
7
10

score 0 · Accepted Answer · answered Nov 03 '15 at 13:51

0

Turned out that the encoding was correct everywhere, except for the browser itself, on the form i was using to test my php file. I don't get why Google Chrome would encode it as European although i saved the html file as UTF-8. Anyways, problem is solved. Thanks for your help, guys =)

answered Nov 03 '15 at 13:51

Kunting

275
1
13

For Chinese, use utf8mb4 in place of utf8. – Rick James Dec 08 '15 at 03:01

Chinese characters in database prepared statement

3 Answers3