Best way to set charset for Zend_Db (or at least better than what I'm currently doing)

Question

I'm using Zend_DB and trying to change the charset to utf8, here is the code:

config.ini :

[development]
db.host = "localhost"
db.username = "root"
db.password = "toor"
db.dbname = "db_whoopdiedo"
db.charset = "utf8"

bootstrap.php :

class Bootstrap extends Zend_Application_Bootstrap_Bootstrap
{

    public function _initAutoload() 
    {
        Zend_Registry::set(
            'config',
            new Zend_Config_Ini(APPLICATION_PATH.'/configs/config.ini', 'development')
        );

        Zend_Registry::set(
            'db',
            Zend_Db::factory('Pdo_Mysql', Zend_Registry::get('config')->db)
        );

        Zend_Registry::get('db')->setFetchMode(Zend_Db::FETCH_OBJ);
        Zend_Registry::get('db')->query("SET NAMES 'utf8'");
        Zend_Registry::get('db')->query("SET CHARACTER SET 'utf8'");
   }
}

i thought it would be enough to add the charset in the config, but he only applys it if i set it directly using:

Zend_Registry::get('db')->query("SET NAMES 'utf8'");
Zend_Registry::get('db')->query("SET CHARACTER SET 'utf8'");

My Question: is there a better way to set the charset, maybe config wise?

Answer 1

Firstly I'd break out your database setup into it's own init function like so:

/**
 * Initiate Zend Autoloader  
 * @return Zend_Db_Adapter 
 */
protected function _initDatabase() 
{
    $resource = $this->getPluginResource('db');
    $db = $resource->getDbAdapter();
    Zend_Registry::set("db", $db);
    return $db;
}

The above example uses resources which are predefined config structures for certain common tasks in Zend Framework. Simply by having the following in my application.ini config file, we can address the resource 'db' in the Bootstrap above:

resources.db.adapter = "pdo_sqlite"
resources.db.params.host = "localhost"
resources.db.params.username = "databaseuser"
resources.db.params.password = "mysecretpassword"
resources.db.params.dbname = APPLICATION_PATH "/data/db/ccymod.db"
resources.db.isDefaultTableAdapter = true

This example is for an sqlite db but MySQL would look similar, but with pdo_mysql and the dbname would not be a file path but a string.

More documentation on the resources can be found here:

http://framework.zend.com/manual/en/zend.application.available-resources.html

Now using the resources config section agin we can add to it the following lines to set the database character set like so:

resources.db.params.charset = "utf8"
resources.db.params.driver_options.1002 = "SET NAMES utf8;"

If you still have problems, take a look at Rob Allen's zend framework tutorial blog post on akrabat dot com and the comments sarting at around number 45 onwards regarding the UTF8 and mysql setup for ZF resources.

Answer 2

Taken from our application.ini:

db.params.driver_options.3 = "SET NAMES 'utf8'"