mysql select help

[c_pattle]

Hey

 

I have two tables called product_list and users_cart.  I am trying to use a select function to retrieve data from both table and then display it as the users shopping cart.  I have used the following sql. 

 

select product_list.product_name, product_list.image, users_cart.* from product_list, users_cart where users_cart.product1 = product_list.product_name and username="chris";

 

However the problem with this is that it only returns the value in product_list.image for the first item in the users shopping cart (users_cart.product1).  Does anyone know how I am get it to return the image for each of the items in the shopping cart (product1, product2 and product3)? 

 

Feel free to ask more question if I haven't explained it well enough. 

 

Thanks for any help

[andrewgauger]

select product_list.product_name, product_list.image, users_cart.*

FROM product_list

JOIN users_cart

ON (users_cart.product1 = product_list.product_name)

WHERE username="chris";

 

If you are looking to return multiple rows from one table and combine it with a single row from another table, you are better off using multiple queries.

 

If you are looking to return a bunch of values regardless of username try this:

 

select product_list.product_name, product_list.image, users_cart.* 
FROM users_cart
LEFT JOIN product_list
ON (product_list.product_name = users_cart.product1)
WHERE username="chris";

 

[beta0x64]

Umm, well, assuming that you made a field for every single thing they could add to their cart (product1, product2, productn . . .), I would recommend that you grab everything with MySQL then manually compare them in PHP. This is the only way to iterate like that.

 

I actually recommend that you make the user's shopping cart something like...

 

id | userid | cart

where cart is filled with ids separated by a |, so...

cart = 1|5|3290|44|7 and so on

 

this way you can get "cart" and explode() it into a list of ids, which you can link up with an inventory. Do you understand what I mean?