MySQL Search Query

[cancer10]

Hi,

You might have seen on websites that they allow you to search by:

"Exact word"
"All Words"
"Any words"

So for the first one i.e exact word, we use the SQL clause WHERE TITLE = 'xyz'
For the "all words" search we use the SQL clause WHERE TITLE LIKE '%xyz%'

(Pls correct me if I am wrong with the above)

I was wondering how do we make an mysql search for search strings like "hello world", such that if either the word "hello" is found anywhere in the TITLE column or the word "world" is found in the TITLE column, it should display the matching results.

Pls can anyone help me out?

Thanks

[koko5]

Hi,

In this case better using RLIKE (http://dev.mysql.com/doc/refman/5.1/en/regexp.html) (instead of union results for each word).
Regards

[abduraooft]

(Pls correct me if I am wrong with the above) There's a fulltext index feature for Mysql, read http://devzone.zend.com/article/1304

[Old Pedant]

"Exact word"
"All Words"
"Any words"

So for the first one i.e exact word, we use the SQL clause WHERE TITLE = 'xyz'
For the "all words" search we use the SQL clause WHERE TITLE LIKE '%xyz%'

No. Quite wrong.

Let's say the search term is "lazy dogs".

EXACT means .... WHERE field LIKE '%lazy dogs%' ...

ALL WORDS means ... WHERE field LIKE '%lazy%' AND field LIKE '%dogs%' ...

ANY WORD means ... WHERE field LIKE '%lazy%' OR field LIKE '%dogs%' ...

But none of these match the best capabilities of a full text search, as Abuduraooft suggests. Google, for example, will do *ALL* of those, giving most points to EXACT and then second most points to ALL and then finally also finding ANY.