Bastiaans Blog

Today I discovered a problem with my PHP/MySQL application. SQL data coming from the Zend_Db logic has the wrong encoding.

Like IMO all modern applications should, my application only uses UTF-8 for displaying and handling all non-ASCII characters. To make the MySQL server understand that I talk UTF-8 to it, I always use the following immediately after connecting:

SET NAMES utf8;
SET CHARACTER SET utf8;

The PDO interface of PHP, that Zend_Db is using, doesn’t use this, resulting in wrong character encoding (probably, the default is still latin-9 or something)
To solve this, you can extend the Zend_Db_Adapter_Pdo_Mysql class and use the extended class instead for setting up the connection in bootstrap.php:

class DbAdapter_Pdo_Mysql extends Zend_Db_Adapter_Pdo_Mysql {

   protected function _connect() {
       // if we already have a PDO object, no need to re-connect.
       if ($this->_connection)
            return;

        parent::_connect();

        $this->query('SET NAMES utf8');
        $this->query('SET CHARACTER SET utf8');
    }
}

Now replace code like this in your bootstrap.ini:

$dbAdapter = new Zend_Db_Adapter_Pdo_Mysql(array(
             'host' => 'localhost',
             'username' => $dbuser,
             'password' => $dbpass,
             'dbname' => $db
             ));

$registry->set('dbAdapter', $dbAdapter);

Zend_Db_Table_Abstract::setDefaultAdapter($dbAdapter);

by this:

$dbAdapter = new DbAdapter_Pdo_Mysql(array(
             'host' => 'localhost',
             'username' => $dbuser,
             'password' => $dbpass,
             'dbname' => $db
             ));

$registry->set('dbAdapter', $dbAdapter);

Zend_Db_Table_Abstract::setDefaultAdapter($dbAdapter);

Wow, problem solved That’s an easy character encoding problemen today

Tags: encoding, mysql, pdo, php, utf-8, zend, zend_db

This entry was posted on Sunday, December 14th, 2008 at 1:14 pm and is filed under English, IT. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.