How to store Emoticons (Emojis) in MySQL database?

Hi there,

I am trying to store Emojis in my database via phalcon db connection. I set table to CHARSET 'utf8mb4_general_ci' and also column.

I also set database service to charset 'utf8mb4':

$di->set('db', function () use ($config) {
    return new \Phalcon\Db\Adapter\Pdo\Mysql(array(
        "host" => $config->database->host,
        "username" => $config->database->username,
        "password" => $config->database->password,
        "dbname" => $config->database->dbname,
        "charset" => "utf8mb4"
    ));
});

When I store a text with emojis directly with a query via phpmyadmin in it, all works fine. Emojis are stored right.

But when I store data via model ($object->save()), the stored data looks like this:

"Prep Wiesngaudi ???????? about Saturday"

Instead of questionmarks there should be emojis.

What do I do wrong?

Thanks a lot!

Best



7.0k
edited May '17

Hi, have you specified the same encoding for your webpages?

<head> <meta charset="utf-8"> </head>

Maybe it's about charset of column?

If it was the column charset, then the data would be garbled when inserting via phpMyAdmin. My guess is it's communication - either when storing or when reading that's causing the problem. When you store via phpMyAdmin, are you then viewing the data in phpMyAdmin? And the emojis render?

When you store via the object, are you again viewing the resulting data in phpMyAdmin?

What version of PHP are you using? Apparently prior to 5.3.6, the charset option was ignored.

You can get the charset of your connection with this:

    public function testAction(){
        $PDO = $this->db->getInternalHandler();
        $objCharset = $PDO->query("SELECT COLLATION('foo')");
        $charset = $objCharset->fetch(PDO::FETCH_NUM);

        echo $charset[0];
        exit();
    }

Just put that in a controller and access that action to see what your actual connection charset is.