PHP PDO: charset, set names?

Solution 1:

You'll have it in your connection string like:

"mysql:host=$host;dbname=$db;charset=utf8mb4"

HOWEVER, prior to PHP 5.3.6, the charset option was ignored. If you're running an older version of PHP, you must do it like this:

$dbh = new PDO("mysql:host=$host;dbname=$db",  $user, $password);
$dbh->exec("set names utf8mb4");

Solution 2:

Prior to PHP 5.3.6, the charset option was ignored. If you're running an older version of PHP, you must do it like this:

<?php

    $dbh = new PDO("mysql:$connstr",  $user, $password);

    $dbh -> exec("set names utf8");

?>

Solution 3:

This is probably the most elegant way to do it.
Right in the PDO constructor call, but avoiding the buggy charset option (as mentioned above):

$connect = new PDO(
  "mysql:host=$host;dbname=$db", 
  $user, 
  $pass, 
  array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
  )
);

Works great for me.

Solution 4:

For completeness, there're actually three ways to set the encoding when connecting to MySQL from PDO and which ones are available depend on your PHP version. The order of preference would be:

charset parameter in the DSN string
Run SET NAMES utf8 with PDO::MYSQL_ATTR_INIT_COMMAND connection option
Run SET NAMES utf8 manually

This sample code implements all three:

<?php

define('DB_HOST', 'localhost');
define('DB_SCHEMA', 'test');
define('DB_USER', 'test');
define('DB_PASSWORD', 'test');
define('DB_ENCODING', 'utf8');


$dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_SCHEMA;
$options = array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
);

if( version_compare(PHP_VERSION, '5.3.6', '<') ){
    if( defined('PDO::MYSQL_ATTR_INIT_COMMAND') ){
        $options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . DB_ENCODING;
    }
}else{
    $dsn .= ';charset=' . DB_ENCODING;
}

$conn = @new PDO($dsn, DB_USER, DB_PASSWORD, $options);

if( version_compare(PHP_VERSION, '5.3.6', '<') && !defined('PDO::MYSQL_ATTR_INIT_COMMAND') ){
    $sql = 'SET NAMES ' . DB_ENCODING;
    $conn->exec($sql);
}

Doing all three is probably overkill (unless you're writing a class you plan to distribute or reuse).

Solution 5:

I think you need an additionally query because the charset option in the DSN is actually ignored. see link posted in the comment of the other answer.

Looking at how Drupal 7 is doing it in http://api.drupal.org/api/drupal/includes--database--mysql--database.inc/function/DatabaseConnection_mysql%3A%3A__construct/7:

// Force MySQL to use the UTF-8 character set. Also set the collation, if a
// certain one has been set; otherwise, MySQL defaults to 'utf8_general_ci'
// for UTF-8.
if (!empty($connection_options['collation'])) {
  $this->exec('SET NAMES utf8 COLLATE ' . $connection_options['collation']);
}
else {
  $this->exec('SET NAMES utf8');
}