从两个表中选择数据，返回数据，并从项目id中获取产品名称

Question

<?php
$transactionOutput = "";
$sql = mysql_query("SELECT * FROM transactions WHERE emailaddress='$email'");
$productCount = mysql_num_rows($sql);
if($productCount > 0) {
while($row = mysql_fetch_array($sql)) {
    $item_id = $row["item_id"];
    $quantity = $row["quantity"];
    $size = $row["size"];
    $price = $row["price"]; 

    $sql = mysql_query("SELECT * FROM products WHERE id='$item_id'");
    $productCount = mysql_num_rows($sql);
    while($row = mysql_fetch_array($sql)) {
    $product_name = $row["product_name"];
    }

    $transactionOutput .= "<tr>";
    $transactionOutput .= "<td align='center'>" .$product_name. "</td>";
    $transactionOutput .= "<td align='center'>" .$quantity. "</td>";    
    $transactionOutput .= "<td align='center'>" .$size. "</td>";
    $transactionOutput .= "<td align='center'>" .$price. "</td>";
    $transactionOutput .= "</tr>";
}

    } else {
$transaction_list = "You have made no transactions yet";
}


?>

我尝试访问来自两个不同表的数据，然后通过将product表中的id与从transactions表返回的item_id进行匹配来返回每个项目的产品名称。这确实输出了正确的信息，但是它只显示了第一个事务，而没有显示其他事务，我知道这也可能是糟糕的编程

Answer 1

问题似乎是您使用相同的变量$sql来存储这两个查询的结果集。这里可能发生的事情是这样的：

1. 运行事务查询并将结果集存储在$sql

中

1. 从事务结果集中读取第一条记录
2. 对于第一条事务记录，运行产品查询并再次将结果集存储在$sql
3. 中输出存储到$transactionOutput
4. next记录中从事务结果集读取

步骤5是问题所在，因为产品结果集覆盖了原始事务结果集$sql。

尝试使用另一个变量进行产品查询：

$rsProduct = mysql_query("SELECT * FROM products WHERE id='$item_id'");
    $productCount = mysql_num_rows($rsProduct);
    while($row = mysql_fetch_array($rsProduct)) {
    $product_name = $row["product_name"];
}

希望上面的内容是有意义的！

：作为附加建议，您可以尝试使用连接查询在同一查询中同时检索事务和产品。这里：

SELECT `t`.*, `p`.`product_name`
FROM `transactions` `t`
LEFT JOIN `products` `p` ON `t`.`item_id` = `p`.`id`
WHERE `t`.`emailaddress` = '$email';

只要循环结果集，你就完成了！

Answer 2

<?php
$transactionOutput = "";
$sql = mysql_query("SELECT * FROM transactions WHERE emailaddress='$email'");
$productCount = mysql_num_rows($sql);
if($productCount > 0) {

while($row = mysql_fetch_array($sql)) {
    $item_id = $row["item_id"];
    $quantity = $row["quantity"];
    $size = $row["size"];
    $price = $row["price"]; 

    $sql = mysql_query("SELECT * FROM products WHERE id='$item_id'");
    $productCount = mysql_num_rows($sql);

   #I suggest you use a different variable (i.e. not $row) here;
    #at this point $row from the first while is still in scope
    #and clobbering it may be causing the problem you see with
    #only the first transaction showing.        
    while($row = mysql_fetch_array($sql)) {
     #If you only need the first product_name, then you don't need the loop, just
     #$row2=mysql_fetch_arrqy($sql);
     #$product_name = $row2["product_name"];

    $product_name = $row["product_name"];
    }

    $transactionOutput .= "<tr>";
    $transactionOutput .= "<td align='center'>" .$product_name. "</td>";
    $transactionOutput .= "<td align='center'>" .$quantity. "</td>";    
    $transactionOutput .= "<td align='center'>" .$size. "</td>";
    $transactionOutput .= "<td align='center'>" .$price. "</td>";
    $transactionOutput .= "</tr>";
}

    } else {
$transaction_list = "You have made no transactions yet";
}


?>