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?

Answer 1

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