Improve mysql search results with wildcards. Joomla 3.3

Question

I have a filter in my mvc model which takes a variable from a search field. It searches titles, among other things, but the search results are poor. This may be a simple syntax problem, but I couldn't see it searching.

I have some item titles like:

"Manolo Blahnik Carolyne Gold Glitter Slingback Pump (35.5, Gold)" or "Belstaff Trialmaster Jacket"

Currently if you search for "manolo blahnik shoes" or "belstaff jacket" you get no results.How do I get matching on ANY of the words from any part of the string?

I have tried adding % to either side of the variable like this %'.$keyword.'% but that doesn't work.

//Filtering search field
    $jinput = JFactory::getApplication()->input;
    $keyword = $jinput->get('keyword', '', 'NULL');
    if($keyword!=''){
         $keyword = $db->Quote('%' . $db->escape($keyword, true) . '%');
            $query->where('( a.title LIKE '.$keyword.'  OR  a.features LIKE '.$keyword.'       OR  a.brand LIKE '.$keyword.' )');
    }

What do you expect? Should a title contain all words or at least one of the words. For example your manolo example has shoes in the search but does have manolo and blahnik. Also of it have to have all words does the order of the words mather? — melvin, May 08 '14 at 21:48
no the order doesn't matter, it should match where there is at least one word anywhere in the string. — larpo, May 09 '14 at 01:39

score 0 · Answer 1 · edited May 23 '17 at 12:13

i think your best bet would be then to explode the search sting you get and create OR's from them

so

$keyword = "manolo blahnik shoes";
$keyWords = explode(' ', $keyword);

$ors = array();

foreach($keywords as $word) {
     $word = $db->Quote('%' . $db->escape($word, true) . '%');
     $ors[] = "a.title LIKE %word";
     $ors[] = "a.features LIKE $word";
     $ors[] = "a.title brand LIKE $word"
}

$queryString = implode(' OR ', $ors');
$query->where($queryString);

Since just one of the words should be in just one of the 3 columns you can have a whole string of OR's.

Of course this can become a rather large query so maybe that is something you would have to keep in mind.

Also this code is an example that you can change to your needs for example make sure that $keywords is not empty before you explode ,escape the keywords that you get or use a prepared statement to prevent sql injection things like that.

For something like this it might even be wise to look into solr for your search instead of doing it directly with mysql. Or if you have a myisam table you might look into FULL TEXT search mysql FULLTEXT search multiple words

Improve mysql search results with wildcards. Joomla 3.3

1 Answers1