[RESOLVED] How to search for multiple words

MichaelHe

I have a database with a text field and a varchar field.
In my search page i do a simple search in both fields like this:

SELECT * FROM table t
WHERE t.name REGEXP '$searchword' OR t.text REGEXP '$searchword')
ORDER BY tn.name

This works fine, but I can't search for 2 different words.
If I search for a word I know is in the text field ex. 'read' it finds the text I need, but if I search for the same word and another words that's in the text ex. 'read this' at the same time, it doesn't find it.

Why can't I search for the 2 words ?

I have tried with REGEXP Like = and so on, but I can't get it to work.
Then I have tried something like WHERE match (t.text) AGAINST ('".$searchword."') but this gives me an error ?

How can I do this ?

best
Michael

Sxooter

Let's say you're searching for red AND blue in either of those fields...

WHERE t.name REGEXP 'red' OR t.text REGEXP 'red'

will find all the red rows, and

WHERE t.name REGEXP 'blue' OR t.text REGEXP 'blue'

would find all the blues right? Now, we just and those and now we need both, in either position:

WHERE (t.name REGEXP 'red' OR t.text REGEXP 'red')
AND (t.name REGEXP 'blue' OR t.text REGEXP 'blue')

Just build another AND for each word you want to search on.

MichaelHe

Thanks for your reply...

I can see what you mean, but the searchword(s) are enteret in a single form field.
So it's a form field where you type " red blue " and click the search button.
So how can I the different words written in the form field put in seperate WHERE statements as you surgest.
Or can it not be don't in any way with a form field ?

What I need is a search like ex. Google and other search engines. In google you would just type " red blue " and it would find the page containing both red and blue.

Sxooter

Just split the words on spaces. There are some preg_xxx functions or split() that should work for that. then iterate the list of words to build your query.

MichaelHe

Thanks a lot... now it works :o)