finding the higher 3 products from keywords

[isaac_cm]

Hello,
I need to make sql that fetch me 3 results of the higher 3 products related to the current product

the search will be based on keywords exists in every product (ex "keys")

this topic is a continue to my previous question
http://www.phpfreaks.com/forums/index.php/topic,116249.msg481732.html#msg481732

thanks

[artacus]

So if your current id is 42, you'll want to pull 39,40,41 right?

[code]
SELECT *
FROM products WHERE id < 42
ORDER BY id DESC LIMIT 3
[/code]

[isaac_cm]

No,
I want the [b]Higher[/b] 3 products that matches common keywords between them

I want the most 3 product similar to the current one to suggest to the customer to buy them in addition to the current selected one (like amazon.com)

thanks

[Eric_Ryk]

How are the keywords stored in your database? Are they just in a field separated by commas or spaces?

[artacus]

Oh so to get 43,44,45
[code]
SELECT *
FROM products WHERE id > 42
ORDER BY id LIMIT 3
[/code]

[isaac_cm]

artacus: you got me wrong, but I wish the problem was that easy, please look at my previous topic
http://www.phpfreaks.com/forums/index.php/topic,116249.msg481732.html#msg481732

Eric_Ryk: the keywords is stored in one field its type is "MEDIUMTEXT" and "," is the separator character

waiting for reply
thanks

[isaac_cm]

I will explain what I need in steps:

1- get the current selected products keywords and store it in a variable or array

2- loop through all the keywords, in each loop I have to get the number of finding this word in all products of the current category (ex: bags)

(ex: keywords = "satin bag(5), classy(9), magnetic clasp(2), Dark(7), Navy(4), Fabric(15)"

3- compare all number and get the higher 3 results (Fabric(15), classy(9), Dark(7)) then get one product id from each of the 3 results to display as a recommended product and exclude the current one of course

thanks

[fenway]

Higher than what?  And how do you intend to pase this keyword field?

[isaac_cm]

ok, nevermind all I need is a good 'product recommendation' system it is the first time I do this so any any idea will be appreciated

thanks

[fenway]

I'm still not sure I understand how you want this to work, or what's in the keywords field, and what the numbers mean.

[artacus]

Ok, I think I'm starting to get you now.
You could make your life much easier if you only stored the id's of the related categories in your field. Once again I'm going to make an assumption that the 15 in Fabric(15) is the category id for Fabric? Yes?

If so, I'd change your keywords to only store the ids like so "2,4,5,7,9,15"

Then you can join your categories like so:
[code]
SELECT cat.*
FROM products AS p
JOIN categories AS cat ON FIND_IN_SET(c.id,p.keywords)
WHERE p.id = 42
ORDER BY cat.id DESC LIMIT 3[/code]

[isaac_cm]

First thank you very much guys for your interest to help me and learning somthing new

I really wish if it so simple , I was adviced to used keywords to get recommend products related to the current selected one

it is based on keywords stored in every product, and by doing select statement I should get the most higher 3 related products to the current one like this

(keyword, num of occurrence), but this solution has a problem that I do not have the prod_id field in the result so I will never know which product to get

(note: In my databse design I can get all product info by the key field prod_id)

however if you have any other "simple" solution or 3rd party script for this it will be great

example site for what I mean:
http://www.qvcuk.com/ukqic/qvcapp.aspx/main.detail.tpl.uktsv.item.tsv/left.html.file.tsvmetadrill,html

(notice the recommended 2 product on the right)

many thanks my friends

[fenway]

The next step would be to break out the comma-separated list into a proper table.

[artacus]

The MySQL manual can tell you more about FIND_IN_SET(). But real quickly, it will let you use a comma seperated field in the same way you use something like WHERE category.id IN(2,3,15,32). BTW, you can't use IN (csvField) because it only matches the first value.

[isaac_cm]

ok, if I made a child table contains keywords for a product in the "products" table ,

then it is only simple join or I have to do something else

thanks