PDO + MySQL and broken UTF-8 encoding

Question

I use the PDO library with a MySQL database in PHP, but if I insert any data encoded in UTF-8, like Arabic words, it’s inserted into the database, but as ?????????.

In my own framework, after I create the PDO connection, I send two queries – SET NAMES utf8 and SET CHARACTER SET utf8. It still doesn’t work.

Example:

loadclass('PDO', array(     sprintf(         'mysql:host=%s;port=%s;dbname=%s',         confitem('database', 'host'),         confitem('database', 'port'),         confitem('database', 'name')     ),     confitem('database', 'username'),     confitem('database', 'password'),     array('PDO::ATTR_PERSISTENT' => confitem('database', 'pconnect')) )); $this->query('SET NAMES ' . confitem('database', 'charset')); $this->query('SET CHARACTER SET ' . confitem('database', 'charset')); 

Workaround: Use the json_encode function to convert data before inserting it to the database, and use json_decode to decode it after fetching. This is how I do it now.

Answer 1

Warning: This answer applies to PHP 5.3.5 and lower. Do not use it for PHP version 5.3.6 (released in March 2011) or later.

Compare with Palec's answer here.

---

Use:

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

It forces UTF-8 on the PDO connection. It worked for me.

Answer 2

You have to set the correct character set for the connection. Add the charset=utf8 option to the DSN (this is MySQL-specific!)

$pdo = new PDO(     'mysql:host=hostname;dbname=defaultDbName;charset=utf8',     'username',     'password' ); 

However, in PHP versions before 5.3.6 (released in March 2011), you must use a workaround as the charset option in the DSN is not supported.

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