PHP: Store Chinese to MySQL Database By Using PDO

by

Problem: When try to store Chinese to mysql database, ??? was stored.

Solution:
– set names utf8;
– set character set utf8;
– set database field’s Collation to be “utf8_general_ci”

SET NAMES indicates what character set the client will use to send SQL statements to the server.

The following code is what is required to make it work.

[php]
<?php
header(‘Content-Type: text/html; charset=utf-8’);
try {
$dbh = new PDO(‘dblib:host=your_hostname;dbname=your_db;charset=UTF-8’, $user, $pass);
$dbh->exec(‘SET CHARACTER SET utf8’);
$dbh->query("SET NAMES utf8");
/* Has problems with persistent connection, so for now, use inpersistent connnection instead*/
}catch (PDOException $e) {
print "Error creating the connection!: " . $e->getMessage() . "<br/>";
die();
}
[/php]

Here is a post about encoding and charset concepts that every programmer SHOULD know.

3 thoughts on “PHP: Store Chinese to MySQL Database By Using PDO”

  1. You resolved my issue 7 years later. I had SET NAMES before SET CHARACTER SET and I couldn’t figure out why some characters wouldn’t save.

  2. Very interesting finding here: I thought I had exactly this in my PHP code – but it seemed to work in PHP 5.4 / MySQL 5.5, but not in PHP 5.3 / MySQL 5.1.

    But there was one tiny difference in my code: I had SET NAMES before SET CHARACTER SET and that’s what broke it all. It looks as if you actually *NEED* to first set the Character Set and THEN the Names to UTF8 – just as you have written in your post.

  3. Do you have any information about the PHP/MySQL versions you use?

    Because here is what I can add:
    – Does not seem to work on PHP 5.3 / MySQL 5.1
    – Works on PHP 5.4 / MySQL 5.5

    With old PHP and MySQL I still end up with “???” in the database for chinese characters

Leave a Comment