JOIN 2 tables to compare values

[matthew798]

Hi guys! Once again i require your endless talents!

 

I have 2 tables in my mysql DB. One table is a list of products a store offers, the other is the cart of the user. I need to find the most efficient way or retrieving the images and prices from the LIST OF PRODUCTS table based on the list of PRODUCT NUMBERS stored in the cart table of the user.

 

List of products offered

 

*************************

Prod # * Price *      img src    *

*************************

001    * 19.99* imgs/pic1.jpg *

002    * 29.99 * imgs/pic2.jpg *

*************************

 

User's Cart

*********************

Prod # * quantity * size *

*********************

001    *    10      *  m    *

002    *    4        *  s    *

********************

 

Basically i just want to retrieve ONLY the rows that i need (ones that the user has added to his/her cart) and ASSOCIATE the img src with the quantity and size so that i can lay it out in a table on a view_cart page...

 

If you want a good laugh just take a look at what ive done so far...

 

$cart = mysql_query("SELECT * FROM ".$session."");

$cart_row_count = mysql_fetch_assoc($cart);
$cart_row_count = mysql_num_rows($cart_row_count);

if($cart_row_count != 0){

$i = 1;

while($cart_result = mysql_fetch_assoc($cart)){
	${"product$i"} = $cart_result['product'];
	${"size$i"} = $cart_result['size'];
	$i++;
}

$i2 = 1;

$query = "product = ".${"$product$i2"}." ";

while($i2 <= $i){
	$i2++;
	$query .= "AND ".${"$product$i2"}." ";
}
}

[Psycho]

You really need to take a look at a tutorial for JOINS because this is as simple as it gets.

SELECT *
FROM cart
JOIN products USING (Prod)
WHERE cartID = $cartID