MySQL: Combine full text search with LIKE search for words with 3 letters

The full text search for MySQL is normally limited to a word length of at least 4 letters. In this tip, I have described, how to change the settings of MySQL to be able to also search for words with 3 letters using the full-text search.

However, there are situations, in which you can not change the minimum word length. For example, if you have a hoster that does not allow changing these settings to your own needs.

In this case, you can still use another trick, that makes the search for shorter words possible by combining the full-text search (for long words) with a LIKE search for all words, that are shorter. In this tutorial, I will show you an approach, how you can do this.

The PHP code

In our example, we would like to search the column "text" for both, words with 3 letters and longer words. First, we want to have a look at the full PHP code, before we get to an explanation:

$searchwords = explode(' ', $searchstring);
$longwords = '';
$shortwords = '';
 
foreach ($searchwords as $word) {
   if (mb_strlen($word, 'UTF-8')>3) {
      $longwords.=' +'.$word;
   } else {
      if (mb_strlen($word, 'UTF-8')==3) {
         $word = strtolower($word);
         if (!in_array($word, array('and','the','him','her')))
            $shortwords.=" AND text LIKE '%$word%'";
      } 
   }
}
 
$mysqlquery = "SELECT * FROM tab WHERE 
MATCH (text) AGAINST ('$longwords' IN BOOLEAN MODE) $shortwords";

That, what we want to search for, is saved in the variable $searchstring. However, the content of this variable should not come directly from a POST-variable to prevent MySQL Injections - but that is another topic.

In the first line, we split $searchstring at a space, so that we get an array $searchwords, in which all of our unique search words of our search term are stored sequentially.

After that, we go through our array $searchwords and we check the word length of the individual words. In the case, the word length is greater than 3, we add the word to the string $longwords. If the word length is exactly 3, we add the word together with a LIKE statement to the string $shortwords. Before, of course, we can test the word on whether we really want to search for this word. In the example, we exclude words such as "and", "the", "him" and "her" from the search, because these words do not really have a relevance. Of course, you can add other words you want to this list. Similarly, we can also extend the script by adding a treatment for two-letter words.

If we have finished the loop, we have separated the long words from the short ones. All of the long words are stored in $longwords, the short words in $shortwords. Finally, we create a MySQL query out of these two variables and we are done.