php - 合并两个查询 MYSQLi

Question

我正在尝试一次性处理 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 
          ?>

但这不是我正在寻找的解决方案。由于我是准备好的陈述的新手，我发现这真的很难!任何帮助都会很棒。谢谢!

Answer 1

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

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