CODEWITHSUNDEEP
phpiosmysqlobjective-cutf-8
Martin Schultz
Martin Schultz

Reputation: 2861

iOS emoticons not stored correctly in MySQL

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!

Upvotes: 3

Views: 2258

Answers (1)

Néstor
Néstor

Reputation: 1327

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)

Upvotes: 2

Related Questions