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
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
