[fw-general] ZF, phpMyAdmin and Unicode [SOLVED]

[Ross]

Concerning the problem with phpMyAdmin unable to read (and export
correctly) Unicode text stored by Zend_Db_Table. In case you are
searching the archives here is the solution.

First of all, I upload the test project to my hoster and wasn't able
to reproduce the problem. So it is environment-related and I am not
filling a bug report. My environment:
    Zend Framework 1.9.0
    Apache 2.2.11
    PHP 5.3.0
    PDO MySQL mysqlnd 5.0.5-dev
    MySQL 5.1.36

The problem here is that parameter
driver_options[PDO::MYSQL_ATTR_INIT_COMMAND] = "SET NAMES 'utf'" is
ignored by the underlying system. The workaround is to change
Zend/Db/Adapter/Pdo/Mysql.php to the following (starting at line 85):

Original:
    protected function _connect()
    {
        if ($this->_connection) {
            return;
        }

        if (!empty($this->_config['charset'])) {
            $initCommand = "SET NAMES '" . $this->_config['charset'] . "'";
            $this->_config['driver_options'][1002] = $initCommand; //
1002 = PDO::MYSQL_ATTR_INIT_COMMAND
        }

        parent::_connect();
    }

Change to:
    protected function _connect()
    {
        if ($this->_connection) {
            return;
        }

        parent::_connect();

        if (!empty($this->_config['charset'])) {
            $initCommand = "SET NAMES '" . $this->_config['charset'] . "'";
            $this->_connection->exec($initCommand);
        }
    }

Code to reproduce error:

/*
    CREATE TABLE `test` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `string` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


    application.ini:
    resources.db.adapter = "pdo_mysql"
    resources.db.params.host = "localhost"
    resources.db.params.username = "root"
    resources.db.params.password = ""
    resources.db.params.dbname = "test"
    resources.db.params.charset = "utf8"
    resources.db.isDefaultTableAdapter = true
*/

    $string = 'αβγδε';

    $table = new Zend_Db_Table();
    $table->setOptions(array(
        Zend_Db_Table_Abstract::NAME => 'test'
    ));
    $id = $table->insert(array(
        'string' => $string
    ));

    $db = new Zend_Db_Adapter_Pdo_Mysql(array(
        'host'     => '127.0.0.1',
        'username' => 'root',
        'password' => '',
        'dbname'   => 'test'
    ));
    $db->query("SET NAMES 'utf8'");
    $result = $db->fetchAll("SELECT * FROM test WHERE id = ?", $id);

    echo "<br />Inserted: " . $string;
    echo "<br />Fetched: " . $result[0]['string'];


Affected environment produces the following:
    Inserted: αβγδε
    Fetched: αβγδε

Should be:
    Inserted: αβγδε
    Fetched: αβγδε