PDO多选查询

Question

I am trying to get rows out of 2 tables. The first query works. But the second doesn't. This is my code:

echo '<table width="100%">';
echo '<tr><td>Product</td><td>Quantity</td><td>Price</td><td>Remove</td></tr>';
foreach ($_SESSION['cart'] as $key => $cartproduct) {
    list($productid, $productquantity) = split("\|", $cartproduct, 2);
    global $db;
    $result = $db->prepare('SELECT name FROM products WHERE ID= :ID LIMIT 1; SELECT price FROM prices WHERE productid = :ID AND quantity = :quantity LIMIT 1');
    $result->bindParam(':ID', $productid);
    $result->bindParam(':quantity', $productquantity);
    $result->execute();
    $row = $result->fetch();
    if($result->RowCount() == 1){
        echo '<tr><td>' . $row['name'] . '</td><td>' . $productquantity . '</td><td>' . $row['price'] . '</td><td><a href="?page=cart&removeproduct=' . $key . '">Remove</a></td></tr>'; //LINE15
    }else{
        unset($_SESSION['cart'][$key]);
    }
}
echo '</table>';

The row name is from the products table and the name price is from the prices table. This is the error I get:

Notice: Undefined index: price in /var/www/html/design2/pages/cart.php on line 15

I am sure the query is working. Can anyone tell my what i am doing wrong?

Answer 1

You are receiving index not defined because of the way your query is structured. You have:

SELECT name FROM products WHERE ID= :ID LIMIT 1; SELECT price FROM prices WHERE productid = :ID AND quantity = :quantity LIMIT 1

This is structured to return 2 result sets. You grab the first result set here:

$row = $result->fetch();

But then you try to access $row['price'] which doesn't exist in that result set. That result set is only the result from the first SELECT. You can see this if you just var_dump($row) and see what your result set looks like.

It looks like you can combine your query so that you get one result set:

SELECT p.name, pp.price FROM products p 
INNER JOIN prices pp ON p.ID = pp.productid 
WHERE p.ID= :ID AND pp.quantity = :quantity 
LIMIT 1;

If you can't combine the queries into one, then you should iterate over your result sets and access the relevant $row index. That would look something like:

while($row = $result->fetch()) {
    if(isset($row['name'])) {
        //do something
    } else if(isset($row['price'])) {
       //do something else
    }
}

Some things to consider:

• You may need a LEFT JOIN instead of an INNER JOIN. This depends on whether or not products always have a corresponding record in the prices table.
• I'm not sure what you're trying to achieve with LIMIT 1. You may need to consider and ORDER BY -- unless it really doesn't matter which record you return in your result.
• You should consider testing your variables $productid and $productquantity to verify they have your intended values after splitting $cartproduct. What if one is empty / blank ?
• You should test your result before trying to access the result array at a specific index.

For example:

if(isset($row['name']) && isset($row['price'])) {
   //echo your results
} else {
  //return an error
}