Zend_Db_Table UTF-8 Characters

Question

Tables in my database are created with a correct UTF-8 charset like this:

CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
...
...
...
...
...
PRIMARY KEY (id)
) ENGINE = INNODB  CHARACTER SET utf8 COLLATE utf8_slovak_ci;

However, when I use Zend_Db_Table to fetch the data from the table with this method:

public function getSingle($id)
{
    $select = $this->select();
    $where = $this->getAdapter()->quoteInto('id = ?', $id, 'INTEGER');
    $select->where($where);
    return $this->fetchRow($select);
}

It returns an object with messed up UTF-8 characters (converted to iso-8859-1 I guess).

When I look in the database through phpmyadmin, it shows up all characters correctly and it also shows correct encoding (UTF-8) so I don't know where's the problem.

How can I solve this problem?

UPDATE:

So I did this and it works:

protected function _initDb()
{
    $this->configuration = new Zend_Config_Ini(APPLICATION_PATH
                                               . '/configs/application.ini',
                                               APPLICATION_ENVIRONMENT);
    $this->dbAdapter = Zend_Db::factory($this->configuration->database);
    Zend_Db_Table_Abstract::setDefaultAdapter($this->dbAdapter);
    $stmt = new Zend_Db_Statement_Pdo($this->dbAdapter,
                                      "SET NAMES 'utf8'");
    $stmt->execute();
}

Is there some better way?

UPDATE2:

I tried this:

protected function _initDb()
{
    $this->configuration = new Zend_Config_Ini(APPLICATION_PATH
                                               . '/configs/application.ini',
                                               APPLICATION_ENVIRONMENT);
    $this->dbAdapter = Zend_Db::factory($this->configuration->database);
    $this->dbAdapter = Zend_Db::factory($this->configuration->database->adapter,
                                        $this->configuration->database->params->toArray()
                                        + array('driver_options' => array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'")));
    Zend_Db_Table_Abstract::setDefaultAdapter($this->dbAdapter);
}

And I get an error:

Fatal error: Undefined class constant 'MYSQL_ATTR_INIT_COMMAND' in C:\wamp\www\bakalarka\application\Bootstrap.php on line 46

Answer 1

Instead of setting driver_options, you can easily do the following.

Your ini file:

db.adapter         = Pdo_Mysql
db.params.host     = localhost
db.params.dbname   = mydb
db.params.username = myuser
db.params.password = mypass
db.params.charset  = UTF8

Note last parameter. Then read your config using Zend_Config_Ini:

$config = new Zend_Config_Ini('application.ini');

And pass database parameters as config subobject:

$db = Zend_Db::factory($config->db);

Answer 2

I guess you're using MySQL? The only way that I know of to force it to return UTF-8 data is to open the DB connection and run the following query :

SET NAMES 'utf8'

or, if you are using the mysqli extension:

mysqli_set_charset('utf8');

should work as well...

Answer 3

Wim is correct. However, there is way to do this without explicitly executing the query, by using the driver_options option for the Zend_Db factory method. I assume you use the pdo_mysql adapter and that you instantiate it with Zend_Db's factory method.

You can pass an additional set of options to the adapter with this factory method. The option for pdo_mysql you are looking for is PDO's MYSQL_ATTR_INIT_COMMAND.

Here's how it works:

$adapterType = 'pdo_mysql';
$adapterConfig = array(
    'host' => 'localhost',
    'dbname' => 'yourdb',
    'username' => 'user',
    'password' => 'yourpassword',
    // here is where the driver options are defined, as an associative array
    'driver_options' => array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
    )
);

$dbAdapter = Zend_Db::factory( $adapterType, $adapterConfig );

As you can see, we're able to utilize PDO's MYSQL_ATTR_INIT_COMMAND option. You probably understand that this constant is unusable in .ini configs. So if you read your db adapter config settings from an .ini file you either have to use the concrete value of the constant in the .ini file, or append/merge the driver_options array later on to the factory method argument at execution (that's what I do), like so:

$dbAdapter = Zend_Db::factory(
 $configuration->database->adapter,
 $configuration->database->params->toArray() +
        array( 'driver_options' => array( PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8' ) )
);

EDIT
I assume this is perfectly possible with only passing one argument to the factory method, like you do in your example, as well. I just accustomed to the habit of passing two arguments to the factory method.