Jason4Ever Jason4Ever - 6 months ago 24
SQL Question

PDO + MySQL and broken UTF-8 encoding

I use the PDO library with 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
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

It has been probably solved years ago, but for future generations' sake:

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

It forces UTF-8 on pdo connection. Worked for me.

Comments