Utf8mb4 & Emoji in a MySQL database (free account)

[koesac]

Does anyone have any tips on saving emoji characters in the MySQL database? I have a normal free account and have tried all the steps to get this to work, is it possible?

I have followed these instructions and these which worked in my local test environment but I can’t seem to get it working here.

I am testing by editing data in phpMyAdminand it isn’t working so this isn’t a connection issue.

Any help would be greatly appreciated

Thanks

[Oxy]

Hi

Usually you will be interested in changing your MySQL collation in order to solve problems with foreign character encodings.

This can be done by using phpMyAdmin and following the instructions below:

1. Enter your Control Panel and click on the phpMyAdmin icon in the Databases box.

2. Select the database you wish to manage from the drop-down menu on the left

3. Click on the Operations tab in the top menu of your phpMyAdmin

4. At the bottom of the page you will see the collation option. You can now select a collation from the drop down menu and click on the Go button.

Please note that after your change the collation of a database only the new tables will be created with the new collation. All other tables remain with the collation, they were initially created.

[koesac]

Hi

Thanks for the really detailed and clear response.

I followed the instructions on a new test database. I set the database, table and column to utf8mb4_bin but am still getting the same error message.

Table & Column:

Error Message:
Warning: #1300 Invalid utf8 character string: '\xF0\x9F\xA4\xAA'

[Oxy]

try update column datatype from text to blob
https://dev.mysql.com/doc/refman/8.0/en/blob.html

The CHARACTER SET of the column needs to be utf8mb4
the connection needs to be utf8mb4

[koesac]

Hi OxyDac

Thank you so much for your help, I have got it working now.

The issue is with the phpMyAdmin panel, if I insert or view the data through my website the emojis work!? It doesn’t seem to be a browser issue as it is the same on Chrome and Safari.

This fixes everything for me as I can add all the data through PHP functions now.

Note to self:

Test the front-end as well before posting in the forum

Thanks

[Admin]

@Oxy

try update column datatype from text to blob
https://dev.mysql.com/doc/refman/8.0/en/blob.html

Just as a side note, using the BLOB data type shouldn’t have to be the answer here. BLOB and TEXT columns are quite bad for performance, and should only be used if your data is too big to fit into a VARCHAR column.

The phpMyAdmin version we’re using is quite old and runs on an old PHP version, so I wouldn’t be surprised if it’s the culprit.

[Oxy]

@koesac I’m glad you did it
and thank you for sharing the joyful news with us