Setting mysql connection charset with PDO

Recently while creating a store locator for a Chinese website I ran into a problem. Some Chinese characters were being displayed correctly and some weren’t. First thing I did was check the charset of the HTTP headers and the database… both were set correctly to utf-8. I figured it had to be a database issue so I began googling and found this.

Apparently you have to set the character set of the connection as well via:

1

SET NAMES 'utf-8'

SET NAMES indicates what character set the client will use to send SQL statements to the server. Thus, SET NAMES ‘cp1251′ tells the server “future incoming messages from this client are in character set cp1251.” It also specifies the character set that the server should use for sending results back to the client. (For example, it indicates what character set to use for column values if you use a SELECT statement.)

Great!

The only issue remaining was the fact that I didn’t want to have to run that query on every page.

I use PDO, and I found out you can use the driver_options argument of PDO to run an initial command.

1
2
3
4
5
6
7
8

$dsn = sprintf( 'mysql:dbname=%s;host=%s', DB_NAME, DB_HOST );
$driver_options = array( PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf-8' );
try {
    $dbc = new pdo($dsn, $user, $pw, $driver_options);
}
catch (PDOException $e) {
    // Handle exception
}

Good reads about character sets

• The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets
• Character Sets / Character Encoding Issues
• Mysql – Connection Character Sets and Collations