Utf-8 encoding using pdo driver

[eluser]dave_123456789[/eluser]
Hello CI Community,

Well I have this problem using pdo driver and utf-8.
When I save my data in my mysql database, I've got this:

Code:

ïéä

instead of this

Code:

ïéä

Here is my database configuration:

$db['default']['hostname'] = 'mysql:host=localhost';
$db['default']['username'] = 'root';
$db['default']['password'] = '';
$db['default']['database'] = 'my_database';
$db['default']['dbdriver'] = 'pdo';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = FALSE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_unicode_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;


Actually, if I add this code in my class model, it's ok, and the data stored looks good:

Code:

function __construct()
{
  $this->db->query( "SET NAMES 'utf8'" );
}

But it's a bit borring to do it with every class model. And the pdo driver should do it automatically, no ?

I also tried to change the driver to mysqli and there is no problem, data stored is good (and without the portion of code in all the class model, of course).
Same database configuration, only change:

Code:

$db['default']['hostname'] = 'localhost';
$db['default']['dbdriver'] = 'mysqli';

I didn't find this problem in any topic, so I guess I've got something wrong, but I can't see it.

I use CI 2.1 and HMVC.

Someone has an idea ?
Thank you.

[eluser]weblyan[/eluser]
Go to system/database/drivers/pdo/pdo_driver.php

Change this:

Code:

function db_set_charset($charset, $collation)
{
  // @todo - add support if needed
  return TRUE;
}

to:

Code:

function db_set_charset($charset, $collation)
{
  $this->query( "SET NAMES ? COLLATE ?",array($charset, $collation));
  return TRUE;
}

Not sure if this is the correct way to do it. Messing with the source code doesn't seem like a great idea. But the comment states that support should be added if needed.

[eluser]toopay[/eluser]
Look at this patch, on line 232, it resolve this issue for pdo-mysql.

[eluser]CroNiX[/eluser]
It looks like you need php 5.3.6 for that patch to work

[eluser]toopay[/eluser]
True, but thats more related with the MySQL PDO itself rather than CI. Anyway, with PHP 5.4 arrive, with traits and many interesting features, also tons of fixed bugs, i feel sorry for people who keep their old PHP version.

For people who not owned VPS or some cloud hosting account (which support the latest PHP version)... if your shared hosting account still use old PHP version, request to them, to upgrade it. If they can't, consider find another hosting which support it.

[eluser]Narf[/eluser]
Actually, that doesn't solve anything. SET NAMES is a work-around for PHP versions prior to 5.3.6, not since.
Here's a pending fix:

https://github.com/narfbg/CodeIgniter/bl...r.php#L615

[eluser]dave_123456789[/eluser]
Hello,

Thank you for the replies.

So I found something which could be interesting. It looks like the $options array in pdo_driver file doesn't need quotes in the key value.

Changes in the ./system/database/drivers/pdo/pdo_driver.php:

Code:

/*CHANGE*/
$this->options['PDO::MYSQL_ATTR_INIT_COMMAND'] = "SET NAMES {$this->char_set}";
/*BY*/
$this->options[PDO::MYSQL_ATTR_INIT_COMMAND] = "SET NAMES {$this->char_set}";

Code:

/*CHANGE*/
$this->options['PDO::ATTR_ERRMODE'] = PDO::ERRMODE_SILENT;
/*BY*/
$this->options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_SILENT;

Code:

/*CHANGE*/
$this->options['PDO::ATTR_PERSISTENT'] = TRUE;
/*BY*/
$this->options[PDO::ATTR_PERSISTENT] = TRUE;

With these changes the data is stored correctly in the database.

I also had some problem with the persistent connection that seems to be solved with these changes.

Do you think it could be the solution?

[eluser]toopay[/eluser]
[quote author="dave_123456789" date="1332259515"]
So I found something which could be interesting. It looks like the $options array in pdo_driver file doesn't need quotes in the key value.[/quote]
Thats already fixed in patch i refer above. The only thing, that you may required (prior to PHP 5.3.6), is to add bang (!) before is_php() method i pointed above.

[eluser]dave_123456789[/eluser]
Thank you Toopay.

If I use the code in the patch (pdo_driver.php), the system doesn't find the _db_set_charset() function.

Code:

Call to undefined method CI_DB_pdo_driver::_db_set_charset() in C:\wamp\www\...\system\database\DB_driver.php on line 171

But I didn't look further, for the moment deleting the quotes is easy and enough for me, so I won't make more change. I guess the next version of CI will solve the problem.

[ wampserver / apache 2.2.17 / php 5.3.5 / mysql 5.5.8 / CI 2.1 + HMVC ]