php - 使用外键从其他表中获取数据

Question

我有 2 个表，每个 cursus 都有一个与外键连接的 cursussoort。我试图从 cursussoort 表中获取数据以显示在 while 循环中。cursussoort_ID 显示的 id 很明显，但我如何从 cursussoort 表中获取“naam”？

<?php
$result = mysqli_query($con, "SELECT * FROM cursus WHERE cursussoort_ID = 1 ORDER BY begindatum ASC");
while ($cursus = mysqli_fetch_array($result)) {
?>
<div class="col-lg-8 col-lg-offset-2 col-md-10 col-md-offset-1">
  <div class="post-preview">
      <h2 class="post-title">
        <?php echo $cursus['cursussoort_ID'];?>
        <?php echo $cursus['begindatum'] . "<br />";?>

      </h2>
      <h3 class="post-subtitle">
        <?php echo $cursus['beschrijving'] . "<br />";?>
      </h3>
  </div>
</div>
<?php } ?>

Answer 1

您应该在 SQL 语句中使用 JOIN 来连接来自 2 个表的数据:

SELECT c.*, cs.* FROM cursus c 
INNER JOIN cs.cursussoort ON c.cursussoort_ID = cs.ID 
WHERE c.cursussoort_ID = 1 
ORDER BY begindatum ASC

然后你可以用HTML显示它:

<?php
$result = mysqli_query($con, "SELECT c.*, cs.* FROM cursus c INNER JOIN cs.cursussoort ON c.cursussoort_ID = cs.ID WHERE c.cursussoort_ID = 1 ORDER BY begindatum ASC");
while ($cursus = mysqli_fetch_array($result)) {
?>
<div class="col-lg-8 col-lg-offset-2 col-md-10 col-md-offset-1">
  <div class="post-preview">
      <h2 class="post-title">
        <?php echo $cursus['cursussoort_ID']; ?>
        <?php echo $cursus['naam']; ?><br />
        <?php echo $cursus['begindatum']; ?><br />
      </h2>
      <h3 class="post-subtitle">
        <?php echo $cursus['beschrijving']; ?><br />
      </h3>
  </div>
</div>
<?php } ?>