php - while 循环中的 SQL 查询

Question

我在 while 循环中有一系列 mysql 查询..就像这样。

 $sql =  mysql_query("SELECT productid FROM cart WHERE userid='$userid'")
 while($row = mysql_fetch_assoc($sql)){
 $pid = $row['product_id'];

 //Second one

 $sql2 =  mysql_query("SELECT barcode,quantity FROM products WHERE id='$pid'")`
 while($row2 = mysql_fetch_assoc($sql2)){
 $quantity = $row2['quantity'];
 $barcode = $row2['barcode'];

 //Third one

 $sql3 = mysql_query("SELECT name,price FROM inventory WHERE product_barcode=$barcode");
 while($row3 = mysql_fetch_assoc($sql3)){
 $name = $row3['name'];
 $price = $row3['price'];

 echo $name.'<BR />'.$price.'<BR />.$quantity';

   }
 }
}

您可以看到这些查询相互依赖，因此使用 while 循环获取所有可能的结果。但我不认为这种查询方式是一种好的做法。这需要很多时间..是吗？我不知道.. 任何人都可以编辑并向我展示查询和输出这些类似查询的更好方法。谢谢

Answer 1

您可以使用单个查询并将表连接在一起:

SELECT
  inventory.name,
  inventory.price,
  products.quantity
FROM
  cart
INNER JOIN
  products ON cart.product_id = products.id
INNER JOIN
  inventory ON products.barcode = inventory.product_barcode
WHERE
  cart.userid = 1

mysqli 也值得研究扩展和使用 prepared statements .

try
{

  $mysqli = new mysqli('hostname', 'username', 'password', 'database');

  if ($mysqli->connect_error)
  {
    throw new Exception($mysqli->connect_error);
  }

  if (!$stmt = $mysqli->prepare("

    SELECT
      inventory.name,
      inventory.price,
      products.quantity
    FROM
      cart
    INNER JOIN
      products ON cart.product_id = products.id
    INNER JOIN
      inventory ON products.barcode = inventory.product_barcode
    WHERE
      cart.userid = ?

  "))
  {
    throw new Exception($mysqli->error);
  }

  if (!$stmt->bind_param('i', $userid))
  {
    throw new Exception($stmt->error);
  }

  if (!$stmt->execute())
  {
    throw new Exception($stmt->error);
  }

  if (!$stmt->bind_result($name, $price, $quantity))
  {
    throw new Exception($stmt->error);
  }

  while ($result = $stmt->fetch())
  {
    echo $name . '<br>' . $price . '<br>' . $quantity;
  }

  if (FALSE === $result)
  {
    throw new Exception($stmt->error);
  }

}

catch (Exception $e)
{
  echo $e->getMessage();
}