[php] PHP PDO: charset, set names?

I had this previously in my normal mysql_* connection:

mysql_set_charset("utf8",$link);
mysql_query("SET NAMES 'UTF8'");

Do I need it for the PDO? And where should I have it?

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

This question is related to php mysql pdo

The answer is


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.


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:

  1. charset parameter in the DSN string
  2. Run SET NAMES utf8 with PDO::MYSQL_ATTR_INIT_COMMAND connection option
  3. 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).


I test this code and

$db=new PDO('mysql:host=localhost;dbname=cwDB','root','',
array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$sql="select * from products  ";
$stmt=$db->prepare($sql);
$stmt->execute();
while($result=$stmt->fetch(PDO::FETCH_ASSOC)){                  
    $id=$result['id'];
}

$con="";
$MODE="";
$dbhost = "localhost";
$dbuser = "root";
$dbpassword = "";
$database = "name";

$con = new PDO ( "mysql:host=$dbhost;dbname=$database", "$dbuser", "$dbpassword", array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$con->setAttribute ( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );   

I just want to add that you have to make sure your database is created with COLLATE utf8_general_ci or whichever collation you want to use, Else you might end up with another one than intended.

In phpmyadmin you can see the collation by clicking your database and choose operations. If you try create tables with another collation than your database, your tables will end up with the database collation anyways.

So make sure the collation for your database is right before creating tables. Hope this saves someone a few hours lol


$conn = new PDO("mysql:host=$host;dbname=$db;charset=utf8", $user, $pass);

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');
}

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");

?>

Examples related to php

I am receiving warning in Facebook Application using PHP SDK Pass PDO prepared statement to variables Parse error: syntax error, unexpected [ Preg_match backtrack error Removing "http://" from a string How do I hide the PHP explode delimiter from submitted form results? Problems with installation of Google App Engine SDK for php in OS X Laravel 4 with Sentry 2 add user to a group on Registration php & mysql query not echoing in html with tags? How do I show a message in the foreach loop?

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to pdo

Pass PDO prepared statement to variables PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes PHP 7 RC3: How to install missing MySQL PDO PHP Connection failed: SQLSTATE[HY000] [2002] Connection refused ERROR: SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it php artisan migrate throwing [PDO Exception] Could not find driver - Using Laravel How to check if a row exists in MySQL? (i.e. check if an email exists in MySQL) PDOException SQLSTATE[HY000] [2002] No such file or directory How do I configure php to enable pdo and include mysqli on CentOS?