Nested SQL Queries???

[vozzek]

Hi everyone,

I'm very new to MySQL, so please bear with me.

I have a shopping cart table called 'tbl_cart', and I need to display it's contents on my View Cart page. This is no problem. However, in that table I'm storing the product ID... but I'd like to display the product name. The name is contained in a different table called 'inventory'.

I'm trying to do a secondary SQL query on the inventory table so I can pull out the name. This should be easy since it's indexed by the product ID. Here's my code:

[PHP]<?php
// current session id
$sid = session_id();

$sql = "SELECT *
FROM tbl_cart
WHERE ct_session_id = '$sid'";
$result = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($result) == 0) {
echo "YOUR SHOPPING CART IS EMPTY";
} else {
$product_id = $row['pd_id'];
while($row = mysql_fetch_array($result)) {
$sql = "SELECT *
FROM inventory
WHERE id = '$product_id'";
$result2 = mysql_query($sql) or die(mysql_error());
$row_inv = msql_fetch_array($result2);

echo $row_inv['name']."<br />";
echo "Product ID#: ".$row['pd_id']. " - - - Quantity: ".$row['ct_qty']."<br />";

}
}
?>[/PHP]

Since I was in the middle of a WHILE loop, I didn't want to reset the $result or $row variables, so I used $result2 and $row2. Apparently I can't do this, because I get a Call to undefined function: msql_fetch_array() error on the line where I'm trying to fetch_array($result2).

Am I going about this the wrong way? I'm sure I am. Any advice on the easy and correct way to do this would be appreciated. Thanks!

[vozzek]

Nevermind... I learned all about JOINS last night. :)

[mwasif]

Good work vozzek. Would you like to share the solution so that others can benefit from it?

[vozzek]

Good work vozzek. Would you like to share the solution so that others can benefit from it?

Sure thing! Here's the code I used:

<?php 
               $sql = "SELECT *
           FROM tbl_cart, inventory
           WHERE tbl_cart.pd_id = inventory.id
           AND tbl_cart.ct_session_id = '$sid'
           ";
?>

Worked great. :)