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

Question

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.

SchizoDuckie · Answer 1 · 2012-08-05T08:22:24.240

121

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 ;)

edited Aug 05 '12 at 08:22

answered Feb 25 '09 at 07:00

SchizoDuckie

9,353
6
33
40

1

:-) My google foo is better, found this before posting, should have sayed so. I will refine my question, I am trying to do it in the config.ini and not in php code. – Itay Moav -Malimovka Feb 25 '09 at 13:21
1

What is up with the slash escaping in that constant? – Kzqai Aug 01 '12 at 16:02
Yes, the question asks how to set utf8. But I think you should consider using utf8mb4 to support the full utf8 character sets if you are using mysql. If you use utf8, then you can lose data depending on how your mysql is configured. – dminer Mar 25 '14 at 16:51
1

Thanks SchizoDuckie, I was looking for a solution for my strange chars problem, and your solution work perfectly! – UserX May 29 '14 at 23:30
Google results lead to stackoverflow :) – Yasen May 01 '16 at 15:21

score 47 · Accepted Answer · answered Mar 31 '09 at 20:11

47

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

answered Mar 31 '09 at 20:11

David Snabel-Caunt

57,804
13
114
132

tawfekov · Answer 3 · 2012-05-26T07:53:28.287

32

just put this in your config

database.params.charset = "utf8"

or after ZF 1.11 this would work to resources.db.params.charset = utf8 that is it

edited May 26 '12 at 07:53

answered Sep 07 '09 at 12:19

tawfekov

5,084
3
28
51

4

This is now the best way to do it :) – David Snabel-Caunt Sep 08 '09 at 17:42

score 4 · Answer 4 · answered Jul 29 '09 at 06:41

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.

score 3 · Answer 5 · answered Dec 03 '10 at 18:42

All this methods shoud work, except in some special circumstances. For example, if you are running a web server locally on a windows machine with php < 5.3.1, only a 'manual' $db->query("SET NAMES 'utf8'"); before your actual query will work. Any other method trying to use MYSQL_ATTR_INIT_COMMAND will fail.

Here's what I learnt today, struggling with this very problem :

You can't refer to PDO::MYSQL_ATTR_INIT_COMMAND in some environments (i.e. mine, specifically I don't know). You have to explicitely use 1002 instead
With Zend Framework 1.11 ( perhaps since 1.8, to be confirmed ), you don't need to set database.params.driver_options.1002 = "SET NAMES utf8" in your config.ini : resources.db.params.charset = "utf8" will be enough for Zend_Db_Adapter_Pdo_Mysql to do it for you.
On windows, you need php >= 5.3.1 for MYSQL_ATTR_INIT_COMMAND to work.
If you replace your php version with 5.3.1 or higher (I also tested 5.3.3), you need to make sure you set a value to pdo_mysql.default_socket in your php.ini. The default empty value won't work (to be confirmed : I read something about this but didn't bother to try without it after finding out about point 5)
You also need to make sure you have '127.0.0.1 localhost' in your windows\system32\drivers\etc\hosts hidden system file (that wasn't a problem for php 5.3.0)

With all this in mind, you should be able to save from yourself a day of googling and keep some of your hairs! ;)

score 1 · Answer 6 · answered Apr 26 '18 at 07:50

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;

score 0 · Answer 7 · answered Mar 06 '09 at 18:59

0

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);

answered Mar 06 '09 at 18:59

score -1 · Answer 8 · edited Feb 10 '13 at 15:07

-1

$table->getAdapter()->query('SET NAMES UTF8');

edited Feb 10 '13 at 15:07

tawfekov

5,084
3
28
51

answered Sep 21 '10 at 18:20

wormhit

3,687
37
46

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

8 Answers8

Linked