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

user_67153 0
Viewed 82651

How to make PDO adapter run SET NAMES utf8 each time I connect, In ZendFramework. I am using an INI file to save the adapter config data. what entries should I add there?

If it wasn't clear, I am looking for the correct syntax to do it in the config.ini file of my project and not in php code, as I regard this part of the configuration code.

zend-db-table pdo zend-framework php
8 Answers

fear my google-fu

$pdo = new PDO(
    'mysql:host=mysql.example.com;dbname=example_db',
    "username",
    "password",
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

first hit ;)

user_180770

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

user_804250

The connection in zend_db is lazy which means it connects on the first query. if you have a static page with no query's in it it will never even connect - even if it is initialized in you bootstrap file.

so running:

$db->query("SET NAMES 'utf8'");

Is not so smart. Big thanks to dcaunt for his solution.

user_1468680

You just have to execute this command before you start queries, you only have to execute it once before the queries and not for every query.

$pdo->query("SET NAMES 'utf8'");

Full example

$servername = "localhost";
$username = "root";
$password = "test";
$dbname = "yourDB";

try {
    $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

    $pdo->query("SET NAMES 'utf8'");

    //set the PDO error mode to exception
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "SELECT name FROM nations";
    foreach ($pdo->query($sql) as $row) {
       echo "<option value='".$row['name']."'>".$row['name']."</option>";
    }


} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$pdo = null;
user_46847970

In your bootstrap file...

$db = Zend_Db::factory($adapter, $config);
$db->query("SET NAMES 'utf8'");

then you save this instance in your registry

Zend_Registry::set('db', $db);
Admin1
Related