0

I am saving Emoji's with charset utfmb4_general_ci, Storing and retrieving are working fine but when i try to search for the information with a string containing Emoji's or special characters i am not getting the result. It always returning empty.

Can somebody help to solve this?

CODE

select * from table where Title LIKE '%Kanye West - \"Bound 2\" PARODY%'

UPDATE:

The search string are like

  1. Kanye%20West%20-%20"Bound%202"%20PARODY

    Stored in database like Kanye West - \"Bound 2\" PARODY

  2. Family%20guy%20

    Stored in database like Family guy \ud83d\ude0e\ud83d\ude14\ud83d\ude01

Please accept my Apologies for not making it clear

The first string is What we sent from the url via HTTP POST and the second is how the data is stored in my table.

The charset of the database table is

utf8mb4_general_ci

Ramaraju.d
  • 1,301
  • 6
  • 26
  • 46
  • can we code source for your search plz ? – jmercier Feb 04 '14 at 08:00
  • I have updated with examples – Ramaraju.d Feb 04 '14 at 09:54
  • I think jmercier was asking about how you perform the actual search, as opposed to how you store the data. Though the examples posted aren't exactly clear - is the first string the query sent in a URL? And the second, what you put into the query (as opposed to the actual database value)? – zinga Feb 04 '14 at 11:30
  • I have updated to make it clear, Thanks – Ramaraju.d Feb 04 '14 at 12:52
  • Well, the stored text and the search text appear to be extremely different. It's no wonder it's not matching. – deceze Feb 05 '14 at 09:38
  • The Emoji's will get encrypted while they send from HTTP POST. So they look like that. any solution? – Ramaraju.d Feb 05 '14 at 09:44
  • If the text stored in the database contains escaping backslashes, you are not escaping it correctly. Text should not be stored escaped. `\u....` is some transportation encoding format, it's not even standard POST data. You should undo that transportation encoding before storing text in the database. You should be storing "Family guy " in the database, no "%", no "\u....", no "\". – deceze Feb 05 '14 at 10:01
  • Ohh ya got your point.. Any idea on how to overcome this type of inconsistent data. What you said seems to be right as we have the necessity to store information cleanly. – Ramaraju.d Feb 05 '14 at 10:07
  • I don't know what you're doing and where the messed up data comes from, so it's hard to give any specific advice. – deceze Feb 05 '14 at 10:15

1 Answers1

1

You need to change your character set to utf8mb4_unicode_520_ci to make emojis searchable. Otherwise they are all treated as the same character.

Danny Ching
  • 41
  • 2