php - Search a MySQL DB field for multiple text

Question

I currently have the following that will search a field within my db for a mention of one word, but would like to know if it is possible that if a customer enter two words, rather than only output anything matching that particular format, it actually splits the words up and returns the result for both words not an exact match:

Currently have

   WHERE title LIKE '%SEARCH TEXT HERE%'..

so if a user currently enter 'FISH' or 'FISH FOOD' then it will return only the columns that have that text within in it as it is.

What i would like to happen is if they entered more than one word then it actually splits that up and then searches and returns the results for all the separate words.

So 'FISH FOOD' would return all rows with the word fish or food with in and not just anything that matched 'FISH FOOD'

Using PHP and MySQL

Thanks

Answer 1

You'll need to break up the string of keywords and do the query like this:

WHERE title LIKE '%SEARCH%' OR title LIKE '%TEXT%' OR title LIKE '%HERE%'..

Though more complex, if you're up to the learning curve of Full Text, I would agree with bpgergo's answer. It has many advantages to simple "LIKE" queries. Consider this:

SELECT * FROM article WHERE title like '%BIO%'

This will return anything with "BIOLOGY", "BIOgraphy", etc. Full Text understands word boundaries so these "challenges" are addresses. Also, you can use the matching Relevance, and use that when sorting your results.

It also considers "stop" words, so if the user put in "Food for my fish", it would still only match for "Food" and "Fish" because "for" and "my" are common words that would be omitted.