homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Need help with MYSQL query.

WebmasterWorld Senior Member 10+ Year Member

Msg#: 4279899 posted 9:28 pm on Mar 10, 2011 (gmt 0)

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. ;))



WebmasterWorld Senior Member 10+ Year Member

Msg#: 4279899 posted 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?

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved