PHP: Store Chinese to MySQL Database By Using PDO

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.

Category >> PHP  
If you want someone to read your code, please put the code inside <pre><code> and </code></pre> tags. For example:
<pre><code> 
String foo = "bar";
</code></pre>

  1. Dominik Deobald on 2014-5-16

    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

  2. Dominik Deobald on 2014-5-16

    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.

Leave a comment

*