PDO + MySQL and broken UTF-8 encoding
character-encoding, mysql, pdo, php
Solution
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.
Problem
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.