can't store entry as string for when there's unicode character in mysql

Question

I have the following string:

\ud83d\udc57 DESTINATION OF SHOPAHOLIC\n\u260e 088216013304 \/ 085713900029\n\ud83d\udcf1 BB 328713BD\n\u2712 LINE : ladydhie\n\ud83d\udcb3 BCA \n\ud83d\udc8b KEEP CALM AND HAPPY SHOPPING!!!\ud83d\udc8b

that I wanted to store as a string in MySQL however doing a simple insert of this gives me empty. Is there something special that I need to do?

I am using Symfony2 and Doctrine as the ORM. So I have an object called User, which has a column called bio and is stored as a string, however when I do something like:

user->setBio(\ud83d\udc57 DESTINATION OF SHOPAHOLIC\n\u260e 088216013304 \/ 085713900029\n\ud83d\udcf1 BB 328713BD\n\u2712 LINE : ladydhie\n\ud83d\udcb3 BCA \n\ud83d\udc8b KEEP CALM AND HAPPY SHOPPING!!!\ud83d\udc8b)

this gives me empty bio stored in the DB. What should I do to be able to correctly store this.

you can escape the string http://stackoverflow.com/questions/5840230/how-to-properly-escape-a-string-via-php-and-mysql — Bob, Sep 15 '13 at 11:18

bobince · Accepted Answer · 2013-09-15T16:29:56.007

Ensure that your table columns' collation is utf8mb4 and the MySQL connection collation is utf8mb4.

MySQL's basic utf8 character set only support characters from the Basic Multingual Plane (3 bytes in UTF-8). Emoji like U+1F457 DRESS aren't in the BMP so can only be stored in full 4-byte form.

how do I check the mysql connection collation?

I'm not familiar with Doctrine but it looks like you should be able to set the charset on a pdo_mysql connection to utf8mb4. You could also set the my.cnf config default-character-set to utf8mb4.

Also, I'm not sure what the syntax is you're using above, but PHP doesn't have \u escapes. To include a character U+1F457 in a PHP string you should either type it directly as '', and save your .php file as UTF-8, or include the UTF-8 byte encoding '\xF0\x9F\x91\x97'.

how do I check the mysql connection collation? – adit Sep 15 '13 at 15:51 — adit, Sep 15 '13 at 15:51

score -1 · Answer 2 · answered Oct 31 '15 at 18:35

-1

I managed to bypass the whole thing by storing the sting in json_format and perfoming json_decode when i access it :)

answered Oct 31 '15 at 18:35

paptom

23
8

can't store entry as string for when there's unicode character in mysql

2 Answers2