php - 合并两个查询MYSQLi

我试图一口气处理MYSQLi准备的语句，因为我需要对得到的变量使用while函数。下面是我的两个查询的代码：

             <?php
              $stmt = $con->prepare("SELECT first_name FROM transactions WHERE order_id = ? ORDER BY id DESC");
              $stmt->bind_param('i', $order_id);
              $stmt->execute();
              $stmt->store_result();
              $stmt->bind_result($name);

              while($stmt->fetch()) { ?>
                  <div class="comment-item">
                  <div class="comment-post">
                  <h3><?php echo $name ?> <span>said....</span></h3>
              <?php }
              $stmt->close();

              $stmt = $con->prepare("SELECT comment FROM reviews ORDER BY id DESC");
              $stmt->execute();
              $stmt->store_result();
              $stmt->bind_result($comment);

              while($stmt->fetch()) { ?>
                    <p><?php echo $comment ?></p>
                  </div>
                </div>
              <?php }
              $stmt->close();
              ?>

一种方法是不使用准备好的语句。这是我为此提出的解决方案：

          <?php
          $result = mysqli_query($con,"SELECT * FROM reviews ");
          while($row = mysqli_fetch_array($result)) :
          $data = mysqli_fetch_array(mysqli_query($con, "SELECT `first_name` FROM transactions WHERE order_id = '{$row['order_id']}'"));
          $name = $data['first_name'];
          ?>
            <div class="comment-item">
                  <div class="comment-post">
                      <h3><?php echo $name ?> <span>said....</span></h3>
                      <p><?php echo $row['comment']?></p>
                  </div>
              </div>
          <?php endwhile
          ?>

但是，这不是我要寻找的解决方案。由于我不熟悉准备好的陈述，因此我发现这真的很难！任何帮助都将是惊人的。谢谢！

最佳答案

使用JOIN一次执行两个查询：

SELECT r.comment, t.first_name
FROM reviews AS r
JOIN transactions AS t ON t.order_id = r.order_id