jecasc - 6:05 pm on Mar 12, 2011 (gmt 0)
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?