How to Make Pdo Run Set Names Utf8 Each Time I Connect, in Zendframework

How to make PDO run SET NAMES utf8 each time I connect, In ZendFramework

Itay,

A very good question. Fortunately for you the answer is very simple:

database.params.driver_options.1002 = "SET NAMES utf8"

1002 is the value of constant PDO::MYSQL_ATTR_INIT_COMMAND

You can't use the constant in the config.ini

How to define the use of utf-8 in Doctrine 2 in Zend Framework application.ini, when using Bisna

works fine for me

resources.doctrine.dbal.connections.default.parameters.driverOptions.1002 = "SET NAMES 'UTF8'"

1002 is the integer value of PDO::MYSQL_ATTR_INIT_COMMAND:

Command to execute when connecting to the MySQL server. Will
automatically be re-executed when reconnecting.
Note, this constant can only be used in the driver_options array when constructing a new
database handle.

zend_db query on connect

Sure, simply pass the 'charset' option in with the adapter params. You can do this via Zend_Config or in code:

$params = array(
    'host'           => '127.0.0.1',
    'username'       => 'webuser',
    'password'       => 'xxxxxxxx',
    'dbname'         => 'test',
    'charset'        => 'utf8'
);

$db = Zend_Db::factory('Pdo_Mysql', $params);

Reference: http://framework.zend.com/manual/en/zend.db.html#zend.db.adapter.connecting.parameters

ä ö character encoding, in PHP, Zend MySQL- application

When characters of the wrong encoding are read to the browser, they come up as � or ?. There is an encoding mismatch somewhere:

• In your browser
• In the database table collation
• In the HTML charset

Those characters would work in Unicode UTF-8, so you should verify that your DB table collation is utf8_bin or similar, as, if you want to store everything in UTF-8 Swedish, you might use utf8_swedish_ci

In your browser, ensure that your content encoding is set to auto-detect.

To create a UTF-8 / utf8_bin table in MySQL, here is an example:

CREATE TABLE `sample` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

To ALTER an existing table to use UTF-8, use the following command:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

This post has a good explanation of whether or not to use CONVERT TO CHRACTER SET utf8

Note CHARSET and COLLATE must both be set, otherwise you will automatically get COLLATE=utf8_general_ci

For HTML, charset can be set using:

<meta charset="UTF-8">

Dynamic connection to DB in Zend Framework 2

After you do your authentication against the (statically configured) Auth DB and consult the (statically configured) Config DB for the dynamic information you need for the Service DB, you could probably instantiate yourself the correct DB-adapter for the Service DB, using something like:

// Config from the Config DB, packaged into an array with keys that 
// are expected by \Zend\Db\Adapter\Adapter
$config = [
    'driver' => 'Pdo_Mysql', // for example
    'user' => 'my-dynamically-obtained-user',
    'password' => 'my-dynamically-obtained-password',
    'database' => 'my-dynamically-obtained-db-name',
    // etc
];
$adapter = new \Zend\Db\Adapter\Adapter($config);

// Now use the $adapter to build queries
$statement = $adapter->query('SELECT * FROM `mytable`');
$results = $statement->execute();

// iterate over the results, etc.

Alternatively, you could feed the $adapter into a model object you create that hides the db-specific query details from the consumer.

See ZF2 docs for \Zend\Db\Adapter\Adapter

How to set database time zone in application.ini

I'm answering my own question just to close this, as the response is already in one of the edits.

resources.db.params.charset

and

resources.db.params.driver_options.1002

as I had in my application.ini cannot be used together, as resources.db.params.charset ovewirites driver_options.1002.

If you need to set the timezone, remove resources.db.params.charset and pass the timezone in the charset in driver_options.
E.g.:

resources.db.params.driver_options.1002 = "SET NAMES utf8, time_zone = 'Europe/London'"

Problems with display of UTF-8 encoded content from a DB

do you use

resources.db.params.charset = "utf8" 

in your config file like (application.ini)?

---

Related Topics