MySQL query to filter records containing any of the words in search string

I have to develop a simple search page that matches search string with field in MySQL table.

Assume database table is 'records' and field in consideration is 'record_title'. Also say following are the 'record_titles' in rows.

word1 word2
word1 word3 word4
word5 word2 word1
word6

Now in search form, say user submit search terms for example 'word1 word7' , 'word1', 'word1 word5', 'word1 word2' etc, it should return recordtitles having at least one of words in search term. It should not take search string as substrings, for example if search term is 'word' it should not return any of the records in above record set.

I tried with following, but it sometimes do not match single words.

MATCH(`recordTitle`) AGAINST ('+word1 +word2' IN BOOLEAN MODE);

How can it (or another solution) can be used to select records containing at least one word matching.

Thank you.

GUIR

Solution

Suppose you have a string to be searched:

variableToBeSearched = 'word word2 word3';

First of all you will have to split it into an array with the delimiter SPACE (depending on the language you are using with MySQL). So that:

Array[0] = 'word'
Array[1] = 'word2'
Array[2] = 'word3'

Then iterate the Array and build the Query String.

string Query = "SELECT * FROM records WHERE ";
if(Array.Length != 0)
{
    For(i=0; i<Array.Length; i++)
    {
        if( i != (Array.Length-1) )
        {
            Query += "record_title LIKE '%" + Array[i] + "% ' OR ";
        }
        else
        {
            Query += "record_title LIKE '%" + Array[i] + "% '";
        }
    }
}
else
{
    // No words found in the String :-)
}

And then execute the QUERY. Please inform if some understanding needs to be cleared, from mine side.