php - 如何在PHP-MySQL中按降序获取特定客户的交易记录？

Question

我想显示MySQL表中存储的某些特定客户的交易记录。

我尝试了以下方法:

<table>
 <tr>
  <th>Transaction ID</th>
  <th>Customer ID</th>
  <th>Description</th>
  <th>Amount</th>
  <th>Date</th>
 </tr>
<?php
    $stmt0 = $mysqli->prepare("SELECT id FROM customers WHERE status = ?");
    $stmt0->bind_param('i',$status);
    $stmt0->execute();
    $stmt0->store_result();
    $stmt0->bind_result($cust_id);
    while ($stmt0->fetch()) 
    {
      $stmt = $mysqli->prepare("SELECT id,description,amount,date FROM transactions WHERE cust_id = ? ORDER BY id DESC");
      $stmt->bind_param('i',$cust_id);
      $stmt->execute();
      $stmt->store_result();
      $stmt->bind_result($id,$description,$amount,$date);
      while ($stmt->fetch()) 
      {
?>
 <tr>
  <td><?php echo $id; ?></td>
  <td><?php echo $cust_id; ?></td>
  <td><?php echo $description; ?></td>
  <td><?php echo $amount; ?></td>
  <td><?php echo $date; ?></td>
 </tr>
<?php 
      }
        $stmt->close();
    }
      $stmt0->close();
?>
</table>  

它根据我的要求显示特定客户的交易记录，但它按“cust_id”分组显示记录。我需要通过按降序排列“id”来显示它们。

请帮忙!

Answer 1

您可以使用SUB QUERIES并在一条 SQL 语句本身中获取结果。喜欢-

<?php
    $stmt0 = $mysqli->prepare("SELECT t.id,t.description,t.amount,t.date FROM transactions t WHERE t.cust_id = (SELECT id FROM customers WHERE status = ?) ORDER BY t.id DESC");
    $stmt0->bind_param('i',$status);
    $stmt0->execute();
    $stmt0->store_result();
    $stmt0->bind_result($id,$description,$amount,$date);
    //PRINT/echo $id,$description,$amount,$date to the front end now
?>

这应该按 $id 的降序返回 $id,$description,$amount,$date 值。

另一种方法是使用SQL JOINS (优于子查询，尽管内部执行几乎相同)

$stmt0 = $mysqli->prepare("SELECT t.id,t.description,t.amount,t.date FROM transactions t INNER JOIN customers c on t.cust_id=c.id WHERE c.status = ? ORDER BY t.id DESC");
//rest of the code remains same