在循环中向sql查询发送两个值

Question

This is the screenshot of table order_items

Code

<?php
$aa = "SELECT * FROM `order_items` WHERE `id` = '$id'";
$result= $mysqli->query($aa);

while($ord = $result->fetch_assoc()) {
    $bb = "SELECT * FROM `products` WHERE `id` = ".$ord['product_id'];
    $results = $mysqli->query($bb);
    $obs = $results->fetch_assoc()
?>
<tr>
    <td>
        <?php echo $obs['name'];?>
    </td>
    <td>
        <?php echo $obs['code'];?>
    </td>
    <td>
        <?php echo $ord['quantity'];?>
    </td>
<?php }?>

There are two tables order_items and products. In the table order_items there are different product_id to the same order_id.

The above code is working properly if there is one product_id related to one order_item but if there are more than one product_id related to one order_item then this code is not working.

Answer 1

You should join the two queries into one. And when querying the order_items table, you should be testing the order_id column to get all the products in an order.

You should also use a prepared query to avoid SQL-injection.

$sql = "SELECT p.name, p.code, oi.quantity
        FROM order_items AS oi
        JOIN products AS p ON p.id = oi.product_id
        WHERE oi.order_id = ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("i", $id);
$stmt->execute();
$stmt->bind_result($name, $code, $quantity);
while ($stmt->fetch()) {
?>
<tr>
    <td>
        <?php echo $name;?>
    </td>
    <td>
        <?php echo $code;?>
    </td>
    <td>
        <?php echo $quantity;?>
    </td>
<?php }?>