How to parse Special Characters with PHP into MySQL?

Question

I'm parsing some data by using PHP and putting it into MySQL. But if the data contains Special Characters like êm-Khê MySQL is outputting following error:

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xEAm-Kh\xEA...'

After i test, MySQL is not the problem. (Table and Column Collation is utf8_general_ci) When i INSERT that êm-Khê Strings into the Table DIRECTLY (manually), it goes into it. So MySQL can accept that data.

So any idea why it is having this error at PHP level?

I do not understand and having about encoding knowledge very well.

Why is it ê transformed into \xEA in the query?

So when i get the data like ê, how can i put it into the Database as it is ê unchanged?

[Handling Unicode Front To Back In A Web App](http://kunststube.net/frontback/). — DCoder, Oct 05 '12 at 05:43

score 3 · Accepted Answer · edited May 23 '17 at 12:18

3

So the problem is that your db connection is not UTF8 but probably LATIN1 so you have to either use PHP's function utf8_encode() or execute SET NAMES utf8 after you connect to the database.

$handle = new PDO("mysql:host=localhost;dbname=dbname",
'username', 'password',
array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

This thread has some good references and explanations

edited May 23 '17 at 12:18

Community

1
1

answered Oct 05 '12 at 05:43

Desislav Kamenov

1,193
6
13

PDO? Sorry i am not getting what do you mean. I am using `mysql_connect`. It is not like that. – 夏期劇場 Oct 05 '12 at 05:54
1

Oh, OK i got it by using php `utf8_encode()` method. Thanks x) – 夏期劇場 Oct 05 '12 at 07:13
It's good to mention that after 5.3.6 you should use `;charset=utf8` as part of the connection string. – Ja͢ck Oct 05 '12 at 08:55

score 0 · Answer 2 · answered Oct 05 '12 at 05:43

0

My guess is that there's an encoding mismatch between PHP and MySQL. ê is not really "transformed" into \xEA, it's just that MySQL does not know what the byte sequence EA is supposed to be and therefore just complains about the raw bytes being wrong. You need to first know what encoding the strings are in in PHP; which depends on where they are coming from. Then you need to set the correct MySQL connection encoding, so MySQL knows what encoding the strings you're sending it are in. How to do that depends on how exactly you connect to the database.

See Handling Unicode Front To Back In A Web App for a rundown of the whole process.

answered Oct 05 '12 at 05:43

deceze

510,633
85
743
889

Hi, the source encoding of string in Php is `utf-8` and it is coming from XML. I mean, the XML is encoded as `utf-8`. – 夏期劇場 Oct 05 '12 at 05:52
Then double check that using `bin2hex` on the query you are sending to the database, and check what your connection encoding is set to. – deceze Oct 05 '12 at 05:55

score -1 · Answer 3 · answered Mar 01 '13 at 14:17

-1

you can use this function. mysql_real_escape_string($user), mysql_real_escape_string($password));

answered Mar 01 '13 at 14:17

A H K

1,758
17
29

How to parse Special Characters with PHP into MySQL?

3 Answers3