0

I am trying to store emojis in MySQL database by changing to utf8mb4.

I have changed the charset and collation of the database, table and column, but it still does not work. I get the following message when trying in phpmyadmin:

Warning: #1300 Invalid utf8 character string: '\xF0\x9F \x98\x84

Indicating that UTF8 is still the charset somewhere. But I am 95% sure the table and column has utf8mb4.

I am guessing the problem is because I need to also change the charset configs in the my.cnf file? Problem is I do not have access to this file through my host (not dedicated)

character_set_client        utf8
character_set_connection    utf8mb4
character_set_database      utf8mb4
character_set_filesystem    binary
character_set_results       utf8
character_set_server        latin1
character_set_system        utf8
collation_connection        utf8mb4_unicode_ci
collation_database          utf8mb4_unicode_ci
collation_server            latin1_swedish_ci

Is there anything I can do on my side, through phpmyadmin for example? Have I missed something?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
John Smith
  • 646
  • 1
  • 9
  • 30
  • 2
    Your problem is the ***connection encoding*** you set when connecting to the database. You change that in the code that connects to the database, no need to futz around with my.ini. Is your concern only phpMyAdmin, or other client code? – deceze Dec 20 '16 at 08:55
  • The plan is to use PHP to store the emojis. But I figured I would test in phpmyadmin first to omit faulty php code as a factor. But perhaps that is my problem? That I can't change the connection encoding of phpmyadmin? But when trying with php, then it just saves emojis as ? Leading me to believe I still need to set stuff up? – John Smith Dec 20 '16 at 08:58
  • Since all the relevant system-wide defaults can be overridden I wouldn't get too obsessed with them. Just make sure you set the appropriate values in your own application (objects and connection). – Álvaro González Dec 20 '16 at 09:03
  • Probably this is the PMA setting you're looking for? https://docs.phpmyadmin.net/en/latest/config.html?highlight=utf8mb4#cfg_DefaultConnectionCollation – deceze Dec 20 '16 at 09:04
  • Thanks deceze, but I do not have access to modify the configuration file, so I cannot change that setting? – John Smith Dec 20 '16 at 14:29
  • 1
    If you will be using the `mysqli` interface, be sure to call `set_charset('utf8mb4')`. – Rick James Dec 20 '16 at 22:39
  • @JohnSmith When you say "...then it just saves emojis as ?" I'm guessing you are viewing those data with MySQL workbench or similar. I think the data might get stored correctly - just fetch it with PHP with proper encoding set on **connection** and inspect with debugger or even write to a text file. – andy250 Feb 07 '17 at 06:25
  • [UTF-8 all the way through](http://stackoverflow.com/questions/279170/utf-8-all-the-way-through) – Martin Feb 18 '17 at 19:06

0 Answers0