mykmallett Posted March 30, 2010 Share Posted March 30, 2010 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 Quote Link to comment Share on other sites More sharing options...
ram4nd Posted March 30, 2010 Share Posted March 30, 2010 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. Quote Link to comment Share on other sites More sharing options...
mykmallett Posted March 30, 2010 Author Share Posted March 30, 2010 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. Quote Link to comment Share on other sites More sharing options...
Rustywolf Posted March 30, 2010 Share Posted March 30, 2010 Maybe use the LIKE function in mysql I think you split the keywords by spaces. Not sure though, look it up Quote Link to comment Share on other sites More sharing options...
mykmallett Posted March 30, 2010 Author Share Posted March 30, 2010 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. Quote Link to comment Share on other sites More sharing options...
Jax2 Posted March 30, 2010 Share Posted March 30, 2010 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. Quote Link to comment Share on other sites More sharing options...
mykmallett Posted March 30, 2010 Author Share Posted March 30, 2010 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. Quote Link to comment Share on other sites More sharing options...
UKMagento Posted April 5, 2010 Share Posted April 5, 2010 I think also you should only use i keyword per page to solve your problem. Quote Link to comment Share on other sites More sharing options...
the182guy Posted April 5, 2010 Share Posted April 5, 2010 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.