We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

mysql utf8 encoding

I can't set correct mysql encoding. I tried to execute $di->get('db')->execute("SET NAMES 'utf8'"); before $application = new \Phalcon\Mvc\Application(); $application->setDI($di); echo $application->handle()->getContent();

in my index.php file, but nothing changed and returned results from DB are not in utf8

How can I set correct utf8 encoding to mysql connection?



16.1k

found solution: $di->getShared('db')->query('SET NAMES UTF8;')->execute();



98.9k

The better way to do that is using the option PDO::MYSQL_ATTR_INIT_COMMAND when doing the connection:

$di["db"] = function() {
    return new \Phalcon\Db\Adapter\Pdo\Mysql(array(
        "host" => "localhost",
        "username" => "root",
        "password" => "1234",
        "dbname" => "test",
        "options" => array(
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
        )
    ));
};

Or:

            $db = new \Phalcon\Db\Adapter\Pdo\Mysql(array(
        "host" => "localhost",
        "username" => "root",
        "password" => "1234",
        "dbname" => "test",
                'charset'   =>'utf8'
            ));

@boston Does this work for you? Which version are you using?

system: Phalcon 0.9.0b2, mysql 5.5.29-0ubuntu0.12.10.1, php 5.4.6-1ubuntu1.1 mysql: base: utf8_general_ci tables; DEFAULT CHARSET=utf8

Thank u very much

i can't get russian letters from database. It seems on windows phalcon cuts the entire encoding to ASCII mb_detect_encoding($cat->name) returns ASCII

system: windows xp, phalcon 0.9.1, mysql 5.5.8, Apache/2.2.21, PHP/5.4.7 in mysql tried all possible encodings



1.0k

use 'charset' =>'utf8' doesn't work for me, option PDO::MYSQL_ATTR_INIT_COMMAND works.

edited Aug '14

'charset' =>'utf8' worked

$di->set('db', function() use ($config) {
    return new DbAdapter(array(
        'host' => $config->database->host,
        'username' => $config->database->username,
        'password' => $config->database->password,
        'dbname' => $config->database->dbname,
        'charset' => 'utf8',
    ));
});


121

i'm facing the same issue, i tried both solutions and it didn't work, could someone help with this, this is my first project with phalcon.

i'm using Windows 8.1 (64-bit) , WAMP + Phalcon.



1.0k

how do you set your db?



121
edited Apr '14

i tried

 'charset'     => 'utf8'

and

PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'

but it still didn't work.

i did a work around, and i put this code into the controller and i got the results to show correctly.

$cities = new Cities(); //table model
$cities->getReadConnection()->query("SET NAMES UTF8");
$results = new Resultset(null, $cities, $cities->getReadConnection()->query("select * from cities order by id"));   
$this->view->cities = $results;

but i don't want to keep doing this every time, i want phalcon to read the data properly.



121
edited Apr '14

i found the solution, seems you need to modify the "services.php" database connection and add one more parameter which is "charset":

/**
* Database connection is created based in the parameters defined in the configuration file
*/
$di->set('db', function () use ($config) {
return new DbAdapter(array(
    'host' => $config->database->host,
    'username' => $config->database->username,
    'password' => $config->database->password,
    'dbname' => $config->database->dbname,
    'charset' => $config->database->charset
));
});

if you notice i've added the "charset" parameter after "dbname" and it seems to work fine now, i think Phalcon guys needs to update the devtools to fix this in the auto generated files.



1.1k

thank you @thamood its worked for me!



2.2k

if you notice i've added the "charset" parameter after "dbname" and it seems to work fine now, i think Phalcon guys needs to update the devtools to fix this in the auto generated files.

I sent Pull Request: https://github.com/phalcon/phalcon-devtools/pull/280