Re: [PHP] People who bought this also bought (amazon style) functionality...logic problem

You have a User table and a Product table, then a linking table that
joins users with products that they have bought.

Given product A you get all users {B} who have bought product A, you
then get a list of all products {C} that they have bought. You group
{C} by product and get a count, order by the count, return the product
and limit the query to return only 5 entries. All this is possible in
one SQL statement, the SQL is left as an exercise to the reader.

As this is an O(n^2) query it won't scale particularily well, so you
should cache the results somewhere, probably in the product table. This
could be regenerated every week or two as it doesn't actually need to be
very current.


David

Ryan A wrote:
Hey all,

I want to write a "module" for xcart (a commercial
'shopping cart') which would be like what Amazon.com
offers when you go to shop on their site... when you
click on a product to see its details you get a little
box below that recommends more products based on what
others have purchased

eg:
If you click on a toy helicopter it would recommend
extra rotor blades,landing gear etc

eg 2:
If you click on Eminem's latest album it would show
you his other two albums that people bought with this
album

etc

I'm trying to work out the logic to do this... and
quite frankly have hit a brick wall.

So far:
-------
I was thinking when someone (Joe) purchases X with
product id Y I would put that in a seperate table,
then when someone (Jane) else comes looking at item Y
I would display everything that Joe bought...
but then it gets a little complicated, if Jane buys Y
and other stuff that Joe didnt buy... how do I merge
what Joe and Jane bought to display to the next
visitor? (I can only dispaly 5 recommendations at
once)



I would appreciate any tips/advise and code you can
give me, would be fantastic of course if you have
worked with xcart or have already done this.

(shameless plug)
I think this was one of the suggested features of
Dan's open source PHPCart
(http://code.google.com/p/phpcart) unfortunatly I dont
have the OO skill to take part in that project and no
time either as we need a cart immd.
Note:
I dont get anything for recommending the above cart
(or xcart for that matter) but if you are interested
in joining a free os cart project, you might want to
think of looking into the above.

Thanks in advance,
Ryan

------
- The faulty interface lies between the chair and the keyboard.
- Creativity is great, but plagiarism is faster!
- Smile, everyone loves a moron. :-)

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

.

Relevant Pages