PDO and UTF-8 special characters in PHP / MySQL?

Question

I am using MySQL and PHP 5.3 and tried this code.

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$con = mysql_connect("localhost", "root", "");
mysql_set_charset('utf8');
if (!$con)
{
  die('Could not connect: ' . mysql_error());
}

mysql_select_db("kdict", $con);
$sql = "SELECT * FROM `en-kh` where english='a'";
echo $sql;
$result = mysql_query($sql);

while($row = mysql_fetch_array($result))
{
  echo $row['english'] . " </br> " . $row['khmer'];
  echo "<br />";
}
?>

=> I got good UTF-8 render display, well done.

But for now I create a class PDO to keep easy to extend and more easy

 class crud {
     // code..
     public function conn()
     {
         isset($this->username);
         isset($this->password);
         if (!$this->db instanceof PDO)
         {
             $this->db = new PDO($this->dsn, $this->username, $this->password);
             $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
             $this->db->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");   
          }
      }
      /*more code here*/
}



/*** a new crud object ***/
$crud = new crud();
/*** The DSN ***/
$crud->dsn = "mysql:dbname=kdict;host=localhost";

/*** MySQL username and password ***/
$crud->username = 'root';
$crud->password = '';
/*** select all records from table ***/
$records = $crud->rawSelect("SELECT * FROM `en-kh` where english='a'");

/*** fetch only associative array of values ***/
$rows = $records->fetchAll(PDO::FETCH_ASSOC);

/*** display the records ***/
foreach($rows as $row)
{
    foreach($row as $fieldname=>$value)
    {
        echo $fieldname.' = '.$value.'<br />';
    }
    echo '<hr />';
}
?>

But it displays my character something like this '????'

I found this link on Stack Overflow, it looks like the same problem i met Special characters in PHP / MySQL

It looks the same as my problem => I tried to fix it, but I still doesn't work.

$this->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAME'utf8'");

Can anyone tell me what the problem is? How can I correct it?

thanks

Pilewort

What is the default charset of the table where you are storing said data? – Goebel

Hmmm I've ever only used just UTF8, but found this post interesting: https://mcmap.net/q/55218/-utf-8-general-bin-unicode. Maybe try setting your db connection to utf_unicode_ci as well, per Alix's post. – Goebel

Answer 1

You're missing an S: it's SET NAMES and not SET NAME:

$this->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");

You also need to un-comment it of course. Also, PDO::MYSQL_ATTR_INIT_COMMAND can not be set with PDO::setAttribute() after you've established your database connection (the constant name says it all), you've to specify it in the constructor using the $driver_options argument, like this:

$this->db = new PDO($this->dsn, $this->username, $this->password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));

An alternative to this is to just execute that very same query immediately after connecting:

$this->db = new PDO($this->dsn, $this->username, $this->password);
$this->db->exec("SET NAMES 'utf8';");

Absquatulate

@Transformer: About that I dunno, it exists according to the documentation (see php.net/manual/en/ref.pdo-mysql.php) and I've used it before... Perhaps you've an outdated PDO version? See my answer update for a possible workaround. – Absquatulate

Thanks you @Alix Axel with option1 using the $driver_options it does not works for me .I did not know what I am wrong ,BUT for for Option2 $this->db->exec("SET NAMES 'utf8';"); ==> it works .thanks so much!:) – Pilewort

this works for me $this->db->exec("SET NAMES 'utf8';"); – Variance

before try, $this->db = new PDO($this->dsn, $this->username, $this->password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'")); I had been utf8_encoding and utf8_decoding with php function, that's really sucks! – Architecture

You know that if the same for SQL server ? – Ultravirus

Life saving answer – Sellars

Answer 2

I had the same trouble and found out after trying out 1000000 different options that the default mysql engine still remains MyISAM.

Verifying that InnoDB is the Default Storage Engine as such:

Issue the command SHOW VARIABLES LIKE 'have_innodb'; to confirm that InnoDB is available at all.

Then issue the command SHOW ENGINES; to see all the different MySQL storage engines.

Look for DEFAULT in the InnoDB line and NOT in the MyISAM line. I realized that my provided had a setting preventing me from changing the default engine using SET storage_engine=MYISAM;. In my case, I contacted my provider and was directed to where I could change the option to be able to change the default engine. Hope this helps!

Housewarming

Answer 3

The possible way to insert Special characters using PDO, just follow the these to steps:

1) Set the Attribute to you connection class.

$this->db->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAME'utf8'");

2) Now you can use htmlspecialchars while query creation and insertion to database:

$ShortDescription   = htmlspecialchars($_POST['ShortDescription'], ENT_QUOTES);
$LongDescription    = htmlspecialchars($_POST['LongDescription'],ENT_QUOTES);

And it will work fine.

Zebu

Answer 4

I suggest always connecting to a database via PDO.

Below is a sample code;

<?php

class connMysql {

    protected static $instance;
    private static $database_type = "mysql";    
    private static $hostname = "localhost";
    private static $user = “database_user”;
    private static $password = “database_user_password”;
    private static $database_name = “your_database”;

    private static $persistent = false;

    private function __construct() {

        try {
            self::$instance = new \PDO(self::$database_type . ":host=" . self::$hostname . ";dbname=" . self::$database_name
                    , self::$user
                    , self::$password
                    , array(
                \PDO::ATTR_PERSISTENT => self::$persistent,
                \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
                \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
                \PDO::ATTR_STRINGIFY_FETCHES => true,
                \PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"
            ));
        } catch (PDOException $e) {
            echo "Connection Error: " . $e->getMessage();
        }
    }

    public static function getInstance() {
        if (!self::$instance) {
            new connMysql();
        }

        return self::$instance;
    }

    public static function close() {
        self::$instance = null;
    }

}

Tiedeman