1

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.

  • Initially I thought you wanted this: joomla.stackexchange.com/a/22712/12352 , but now I see that you don't want LIKE, you want to allow accented or non-accented letters to qualify. Do you have a short list (perhaps 4 or 5 different values that express the scope of this issue) of realistic values so that a fully resolving recommendation can be made? Does your Joomla site have a primary language or is it multilingual? What is the charset and collation of your table? stackoverflow.com/q/10023776/2943403 , stackoverflow.com/q/37665452/2943403 – mickmackusa Dec 26 '19 at 20:22
  • dba.stackexchange.com/a/190975/157408 ,dba.stackexchange.com/q/122607/157408 Otherwise, if you are unable or unwilling to modify your database settings, you could use some convoluted REGEX or create a new column in your table that has all of the accented character pre-reduced to their relative single-byte form and only make search queries that also have multibyte characters reduced to single-byte characters. I think we'll need some feedback from you. – mickmackusa Dec 26 '19 at 20:40
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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.