I was attempting to store chat logs in a MySQL database where the message field is utf8_general_ci

However, when it came across an emoji, it would choke, and the message field would only include text up to the emoji character.

I changed the message field to be utf8mb4_general_ci, which seemed to be suggested upon googling, and now emoji characters are replaced in the database with four question mark characters, instead of choking.

What is the best way to encode/decode emoji characters so that they can be accurately represented? (Ideally with PHP)

Re: Storing emojis in MySQL database 80 80

Got it sorted!

I had to connect to MySQL with the right character set. Just changing it in MySQL wasn't enough.

Re: Storing emojis in MySQL database 80 80

Actually, I spoke too soon, unfortunately.

While this works in some places, in other places the script is crashing with the CodeIgniter error message: Unable to set database connection charset: utf8mb4

Re: Storing emojis in MySQL database 80 80

Have you changed the character set and collations (from utf8 to utf8mb4) of the database and tables you're dealing with? As far as I remember in MySQL you need to do them individually, the changes don't cascade.

Re: Storing emojis in MySQL database 80 80

Have you changed the character set and collations (from utf8 to utf8mb4) of the database and tables you're dealing with? As far as I remember in MySQL you need to do them individually, the changes don't cascade.

Yes, I have. I'm using CodeIgniter PHP framework (as mentioned many times throughout DaniWeb). When telling CI to use utf8mb4, the Dazah web app correctly handles the data as utf8mb4, but silently throws a trillion error messages in the error log saying: Unable to set database connection charset: utf8mb4 ... However, the Dazah API completely chokes on the error message and does not output anything, therefore breaking the Dazah API and DaniWeb right along with it.

Re: Storing emojis in MySQL database 80 80

This turned out to be a problem in the php-mysql module compiled with the default OS, switching from php-mysql to php-mysqlnd (the mysql native driver), enabled the character set to be used.

Custom compiling, or using the nd package should fix the issue.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.