0

I'm trying to display emoji on screen, from the database, but some emojis works, some others doesn't.

Database version: 10.5.4-MariaDB

Database Collation: utf8mb4_general_ci

Table charset: utf8mb4_general_ci

On PHP I'm using a simple select (SELECT * FROM table_name)

And the table has the lines:

1 - Backlog ️
2 - Prioritized 
3 - Doing ✏️
4 - On Hold ⏰
5 - Done ✅
6 - Archived

(Are exactly that, the direct emoji, without encoding)

When I try to do a "print" or "echo" on the select resultset, I got this:

1 - Backlog ?
2 - Prioritized ?
3 - Doing ✏️
4 - On Hold ⏰
5 - Done ✅
6 - Archived ?

But, if I try put the emoji directly on the code, like: <div class="title">Backlog ️</div> It's working fine.

What can I do to store it on the database, and select/print without problems?

I really don't know if I'm doing it right, if should encode/decode before/after.

I've already tried to use utf8_encode and utf8_decode, but didn't work.

Leonardo
  • 135
  • 1
  • 2
  • 11
  • The missing characters are all higher level code points, so you're not using utf8mb4 somewhere. What does `show create table whatever` give you? – miken32 Dec 04 '20 at 01:31
  • @miken32 Thanks for the answer, you gave me a direction, and I found the solution (I'll answer below) – Leonardo Dec 04 '20 at 10:02
  • The column and the connection need to be specified as `CHARACTER SET` utf8mb4` because some of those Emoji are 4-byte. Please provide `SHOW VARIABLES LIKE 'char%';` and `SHOW CREATE TABLE` so we can verify. (Meanwhile, encode/decode functions will not help.) – Rick James Dec 17 '20 at 08:37

1 Answers1

1

First, thanks to @miken32, for the direction.

I've found a solution that worked for me:

On the PDO connection, I wasn't declaring the charset collection, so the solution was:

From:

$connection_string = "mysql:host=localhost:3307;dbname=database_name";

To:

$connection_string = "mysql:host=localhost:3307;dbname=database_name;charset=utf8mb4;collation=utf8mb4_unicode_ci";

And all keep same:

$options = array(
                PDO::ATTR_PERSISTENT => true,
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
            );
new PDO($connection_string, $username, $password, $options);
Leonardo
  • 135
  • 1
  • 2
  • 11