1 1 Vote

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

Tip by Stefan Trost | 12/11/2011 at 22:05

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.

ReplyPositiveNegativeDateVotes
11 Vote

I think there are some errors in your code, at least in my case I had to make some changes for this code to work. Changes and updated code below:

- Incorrect variable used in array check: $val should be $word
- Helpful to use strtolower on $word to catch all case variations of the words (i.e. The, AND, etc.)
- Had to change AND to OR in the query 

if (!in_array((strtolower($word)),array('and','the','him','her')))
$shortwords.=" OR text LIKE '%$word%'";
27/01/2016 at 07:52

ReplyPositive Negative

Stefan Trost

Show Profile | Message
Avatar
11 Vote

Thank you very much, I have corrected the wrong variable name and I have added strtolower() to ensure matching uppercase and lowercase writings.

The AND or OR depends on what you are searching for - do you want to retrieve all records containing all words (AND) or at least one word (OR).
29/01/2016 at 14:53

Positive Negative
Reply

About the Author

Avatar AuthorYou can find Software by Stefan Trost on sttmedia.com. Do you need an individual software solution according to your needs? - sttmedia.com/contact
Show Profile | Message

 

Related Topics

MySQL: Sort by Relevance
Question | 1 Answer
The Askingbox Search
Info | 0 Comments

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own articles on askingbox.com. How to do.