I have a keyword string on which I want to do full text searching.

The current search query in the model is as follows:

    $keyword = $jinput->get('keyword', '', 'NULL');
         $keyword = $db->Quote('%' . $db->escape($keyword, true) . '%');
            $query->where('( a.title LIKE '.$keyword.'  OR  a.features LIKE '.$keyword.'  OR  a.brand LIKE '.$keyword.' )');

However this returns very poor search results. For example, the search string Google Nexus 5 matches, whereas Google 5 Nexus does not. What is the correct way to convert this query to fulltext searching in Joomla 3.3

  • Do you want results that only have all 3 words? – TryHarder May 12 '14 at 6:24

NB. this isn't tested code just typed into the browser so caveat emptor

The first step would be to check the search term for spaces and then explode() it it into an array() that contains each of the space delimited strings. e.g.

$keywordArray = explode(" ", $keyword);

If you want to only find content that has all keywords in it, then you also need to use an AND rather than an OR. By the way, the $db->quote() call you have automatically performs an escape() the way you're using it (I'm not sure if it detects the previously escaped text with the extra escaping or not in your case).

So, to build your where's you could loop through the array with something like:

    $keywordArray = explode(" ", $keyword);

    foreach ($keywordArray as $keyword)
        $searchTerm = $db->Quote('%' . $keyword . '%');
        $query->where('( a.title LIKE '.$searchTerm.' AND a.title LIKE '.$searchTerm.' AND a.title LIKE '.$searchTerm.' )');
        $query->where('( a.features LIKE '.$searchTerm.' AND a.features LIKE '.$searchTerm.' AND a.features LIKE '.$searchTerm.' )');
        $query->where('( a.brand LIKE '.$searchTerm.' AND a.brand LIKE '.$searchTerm.' AND a.brand LIKE '.$searchTerm.' )');

Having said that, this could be some serious load on MySQL, it's a relational database after all not a search engine. You may want to do things like:

  1. Give the user the option to search in just one of those columns
  2. Limit the number of words on input, imagine a search with 10 or more words in it.
  3. Look at options like MATCH and MATCH AGAINST

I'm facing the same issue especially when you have large data sets. I tried also the MATCH and MATCH AGAINST solutions but they were not really helpful for full text search. At the end I wrote my own finder plugin and do migrate my extensions for full text search to use smart search. Don't know if this is an option for you.

Your Answer

