3

I'm developing a chat app for iOS and I'm trying to store the chat text in a MySQL database. Normal text works proper, even special chars like german 'umlaute'. I'm sending the text as Json to the server.

What not works is if I use emoticons from the iOS emoticons keyboard, like this:

{"userid":"1","message":"Test 123 ⚽️❤️"}

I first had the MySQL database tables as UTF-8. The result was that the chars: "Test 123 ⚽️❤️" got stored and the other emoticons got truncated. I have to add I saw the full string like above in the PHP log, so it seems not to be an issue that PHP does not receive it correctly. This is how it looks in the php log:

INFO  - 2014-12-16 22:25:04 --> New Feed Post. Json: {"userid":"1","message":"Djjdhd"}

The issue is that MySQL does not store it correctly. I changed the whole database to UTF-16 and even UTF-32 as I thought those chars are not in the UTF-8 range but in the UTF-16 range.

Result was that the emoticons get not truncated yet, but appear as question marks in the database, like this:

"Test 123 ⚽️❤️????????????"

Any idea why these emoticons do not get stored proper?

I saw this post (iPhone emoticons insert into MySQL but become blank value) but it appears that PHP receives it correctly.

Thanks!

Community
  • 1
  • 1
Martin Schultz
  • 2,571
  • 2
  • 26
  • 31
  • you need to make sure the SAME charset is used through the ENTIRE rendering pipeline. you've forgotten something, somewhere, e.g. the php->mysql connection. – Marc B Dec 16 '14 at 21:30
  • 1
    http://stackoverflow.com/questions/279170/utf-8-all-the-way-through – Marc B Dec 16 '14 at 21:30
  • 1
    Ahhh thanks! It was CodeIgniter being set to UTF-8 in the database config file. I set it to UTF-16 and it works. Thanks Marc, the second posting was the correct answer! – Martin Schultz Dec 17 '14 at 07:54

1 Answers1

2

I've solved a similar problem setting up the table and column to utf8mb4:

For each table:

ALTER TABLE table_with_emojis CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

And each of the columns of that tables:

ALTER TABLE table_with_emojis CHANGE column_with_emojis VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

As it is explained in this post http://blog.arkency.com/2015/05/how-to-store-emoji-in-a-rails-app-with-a-mysql-database/ (it is for Ruby on Rails but you can apply it to PHP).

There is a excellent answer at SO with much more details and other possible problems: https://stackoverflow.com/a/279279/2412686

As walapu has commented, be careful if you already have an emoji at your database (it wasn't my case as I couldn't store any emoji before)

Community
  • 1
  • 1
Néstor
  • 1,317
  • 13
  • 24