php - 在另一个查询中使用查询结果

Question

这是我的第一个查询，我想将提取的多个 itemID 用于另一个查询。

$conn = new mysqli(server, dbuser, dbpw, db);
$email = $_GET['email'];
$querystring = "SELECT itemID from mycart where email = '".$email."' ";
$result = $conn->query($querystring);
$rs = $result->fetch_array(MYSQLI_ASSOC);

需要的第二个查询

$query = "SELECT * from CatalogueItems where itemID = '".$itemID."'";

我如何运行这 2 个查询？

Answer 1

首先，您的代码对SQL injection开放相关攻击。请学会使用Prepared Statements

现在，从查询的角度来看，您可以利用 JOIN 将其变成单个查询:

SELECT ci.* 
FROM CatalogueItems AS ci
JOIN mycart AS mc ON mc.itemID = ci.itemID 
WHERE mc.email = $email  /* $email is the input filter for email */

使用 MySQLi 库的 Prepared Statements 的 PHP 代码如下所示:

$conn = new mysqli(server, dbuser, dbpw, db);
$email = $_GET['email'];

$querystring = "SELECT ci.* 
                FROM CatalogueItems AS ci
                JOIN mycart AS mc ON mc.itemID = ci.itemID 
                WHERE mc.email = ?";  // ? is the placeholder for email input

// Prepare the statement
$stmt = $conn->prepare($querystring);

// Bind the input parameters
$stmt->bind_param('s', $email);  // 's' represents string input type for email

// execute the query
$stmt->execute();

// fetch the results
$result = $stmt->get_result();
$rs = $result->fetch_array(MYSQLI_ASSOC);

// Eventually dont forget to close the statement
// Unless you have a similar query to be executed, for eg, inside a loop
$stmt->close();