Need help with MYSQL query.

[jecasc]

I am trying to write a new search for my online shop. I have created a table "words" with all words that appear in product names. And another table "words_to_products" so I can see to what products the words relate. Bascially it looks like this:

Product names:
"Red Widget" with product id: 0001
"Blue Widget" with product id: 0002
"Brand Red Widget" with product id: 0003

Table "words":
id | word
1 | red
2 | widget
3 | blue
4 | brand

Table "words_to_products":

word_id | product_id
1 | 0001
1 | 0003
2 | 0001
2 | 0002
2 | 0003
3 | 0002
4 | 0003

Now if someone enters for example: red widget
it should search in "words" and get the ids of the two keywords and then look in "words_to_products" where all "id"s match a product and return: 0001 and 0003
Since those are the "products_id"s for the two products that contain both words.

If someone could point me into the right direction how that could be achieved this would be very helpful. I know I have to JOIN the tables but I do not know how to get to the desired results.

(I want to do the search with an index and not search through the products database with simply: SELECT * from products WHERE products_name like = '%keyword%' OR products_name like = '%keyword%', so this is not the answer I am looking for. ;))

[jecasc]

Ok, I have come up with something which looks like this for three keywords:

SELECT wtp.products_id FROM words AS w JOIN word_to_products AS wtp ON w.id = wtp.word_id WHERE w.word like '%$keyword1%' OR w.word like '%keyword2%' OR w.word like '%keyword3%' GROUP BY wtp.products_id HAVING (count(wtp.products_id) = $count)

$count is the number of keywords.

Basically what this does, is group the results by how many keywords are matched and then return only the results where the number of matches equals the number of keywords.

The problem: It also returns results where the keywords are not distinct.

Let's say the product is called:

"Bluesguitar blue by BrandX".

And someone searches for: blue BrandX flute.

Then it will also return ""Bluesguitar blue by BrandX".

Because the blue is matched twice: In Bluesguitar and blue + the match from BrandX, makes three matches which equals the number of keywords.

Does anyone have an idea how to solve this?