CODEWITHSUNDEEP
mysqlutf-8special-characterssql-like
Baylock
Baylock

Reputation: 1260

Mysql LIKE Clause and accents

I'm creating a search engine with php and mysql.

It works but if I search for instance the word "publié", it will not find anything but if I search the word "Publie", it will show results embedding the term "publie" AND "publié".

How can I make the search accent-sensitive to get only the results matching exactly what I asked for, with or without accents?

Here is a typical request I use:

$requete4  = mysql_query("
    SELECT 
        title,
        description, 
        CONCAT(
            IF (title LIKE '%".search_word."%', '1', '0'),
            IF (description LIKE '%".$search_word."%', '1', '0-')
        ) AS match_bitmask
    FROM mytable 
    WHERE 
    (
        title LIKE '%".$search_word."%' OR 
        description LIKE '%".$search_word."%'
    )
    ORDER BY id DESC 
");

Note1: all my tables, varchars and text fields in Mysql are utf8_general_ci.

Note 2: I tried to add "COLLATE utf8_general_ci" at the end of my request but I get an error message.

Thank you for your help.

Upvotes: 2

Views: 3283

Answers (1)

eggyal
eggyal

Reputation: 126035

How can I make the search accent-sensitive to get only the results matching exactly what I asked for, with or without accents?

Use the utf8_bin collation. Either—

  • change the collation of the columns:

    ALTER mytable
  MODIFY title       <datatype> COLLATE utf8_bin,
  MOFIFY description <datatype> COLLATE utf8_bin;

  • explicitly specify the collation to be used in each LIKE expression:

    SELECT 
        title,
        description, 
        CONCAT(
            IF (title LIKE '%".$search_word."%' COLLATE utf8_bin, '1', '0'),
            IF (description LIKE '%".$search_word."%' COLLATE utf8_bin, '1', '0-')
        ) AS match_bitmask
    FROM mytable 
    WHERE 
    (
        title LIKE '%".$search_word."%' COLLATE utf8_bin OR 
        description LIKE '%".$search_word."%' COLLATE utf8_bin
    )
    ORDER BY id DESC

Upvotes: 3

Related Questions