Recommended products

[mykmallett]

Hi, I'm writing a PHP mySQL ecommerce website for a uni project. I'm having difficulty thinking of an effective and efficient way to do the "you may also be interested in" part.

 

I was thinking of having a table of meta data like such

 

meta_id | meta_keyword

1          |    Gold

2          |    Silver

 

etc.

 

Then having a second table that was filled in by admin when the product was put in the catalogue that was populated by choosing one of the meta keywords available (or adding one to the metadata table)

 

product_id  |  meta_id

2                |    4

2                |    5

2                |    8

3                |    3

3                |    4

3                |    8

 

etc

 

 

So basically when the product page was called it would see which meta keywords were associated with the product, then bring up a list of products with matching keywords.

 

However, firstly im not sure if this is the right approach. And secondly, I'm not sure if there's a SELECT statement that would allow me to search the meta table well, or with any relevance.

 

Am I on the right lines?

 

Thanks in advance

Mike

[ram4nd]

You can do it like this, but you can only have 1 keyword per page. I mean like you cant use 1 keyword on multiple pages.

[mykmallett]

You can do it like this, but you can only have 1 keyword per page. I mean like you cant use 1 keyword on multiple pages.

 

 

I dont see why that is? If its because of a primary key issue in the 2nd table, I would have the product_id and the meta_id as a clustered primary key, or just have a third auto-incrementing column.

 

Or am i missing something? Can you elaborate please.

[Rustywolf]

Maybe use the LIKE function in mysql

 

I think you split the keywords by spaces.

Not sure though, look it up

[mykmallett]

I cant find anything that tells me how to search for multiple keywords in LIKE and how to sort by relevance, this is something I will investigate further when my original question has been commented on. My question is more 'is this a feasible method'. Im not asking for code, im just asking for help with my logic. There's no point me spending hours trying to slap together poor SQL statements if someone can point me to something more economical. Sadly, searching for 'recommended' followed by anything at all on here and on google is not very helpful.

[Jax2]

Why not just make a manual cross sell module for the admin to set cross selling items?

 

For example,

 

Enter the item number: ___

Enter the cross sell item number: ___

 

and just create a table that keeps track of which products are cross sold on the main product... so:

 

product -- cross_sell

1 -- 23

1 -- 9

1 -- 20

2 -- 15

 

...etc.

 

Then it's as simple as calling that table and finding each record that has main product of the current product and displaying a small section for each cross sell product.

It's how my current shop works and it's perfect.

 

[mykmallett]

Why not just make a manual cross sell module for the admin to set cross selling items?

 

For example,

 

Enter the item number: ___

Enter the cross sell item number: ___

 

and just create a table that keeps track of which products are cross sold on the main product... so:

 

product -- cross_sell

1 -- 23

1 -- 9

1 -- 20

2 -- 15

 

...etc.

 

Then it's as simple as calling that table and finding each record that has main product of the current product and displaying a small section for each cross sell product.

It's how my current shop works and it's perfect.

 

I saw something like that and wondered whether to go down that road. I wanted something that was slightly more intuitive tho, that links items via a description, rather than having to choose each item to link. After a few hundred products, doesn't that become a little overwhelming? Having to add each relevant item to the cross sell list?

 

I imagine this is something that is very useful for selling , say batteries with a walkman...but not for finding things that are similiar based on your previous choices. I'm trying to imagine how Amazon build a list of recommendations, because, at least with music, it's very effective.

 

I'm not slating your method by the way, these are genuine questions. I think I will do the cross selling method also for extra school boy points.

[UKMagento]

I think also you should only use i keyword per page to solve your problem.

[the182guy]

mykmallett, you're on the right lines with your meta table. As pointed out though, one keyword per product is not really effective. You can solve it by creating a slightly more sophisticated product tag system for example:

 

table 1: product_tags (id, name).

Data example:

 

1, Gold

2, Intel

3, Outdoor

4, Timepiece

 

tabe 2: product_tag_assocs (id, tagid, productid)

Data example (say productid 99 is a Gold watch, productid 98 is a clock):

 

1, 1, 99

2, 4, 99

3, 4, 98

 

Note table 2 just stores 3 ID's (the first id is not relevant as it's only the autoincrement PK), this is the tag association table). This method allows you to add as many tags to a product as you like. You can then just do a lookup to see what products share the same tags as the current product and use those as the 'related or recommended products'.

 

On the administration page, you can pull out all of the tags and allow the use to select them using checkboxes, or let the create new tags.