How to match database values regardless of accents on letters?

Question

With the following LIKE condition in my WHERE clause, I only get the exact match.

. ' LIKE '. $db->quote($db->escape($artist)), 'AND') .

When $artist is, for example Beyoncé, it only returns a value when $artist is exactly Beyoncé.

I want it to return a value as well when the database value is Beyonce.

I found solutions where there is no variable (something with %), but I can't get this to work.

Answer 1

Method 1 - Store additional column

First method is to store additional column in your artists table with only a latin characters in it (eg. beyonce).

Method 2 - Update the LIKE query

MySQL have the ability to replace exacly one character with a wildcard. In your case the query would look like this:

SELECT * 
FROM `#__artists` 
WHERE `artist` LIKE "beyonc_"

This query would match:

• Beyoncé
• Beyonce
• Beyonci
• Beyonct

But would not match:

• Beyonc
• Beyoncee
• Beyoncét

Method 3 - Use a full text search

Last method is to use full text search. It would require some changes in table. The columns you try to match against would need to be of type Text. Then you can run a query like this:

SELECT * 
FROM `#__artists` 
WHERE 
    MATCH (`artist`) AGAINST ('Beyonce' IN NATURAL LANGUAGE MODE)

This should return all the results that match a string.

Alternatively you can provide a score column like this:

SELECT 
    *, MATCH (`artist`) AGAINST ('Beyonce' IN NATURAL LANGUAGE MODE) AS `score` 
FROM `#__artists` 
WHERE `score`>0

and decide what results are best by checking score column. To read more about full text search click the link above.