CODEWITHSUNDEEP
phpmysql
Heis
Heis

Reputation: 568

Get data from other table with foreign key

I got 2 tables every cursus has a cursussoort connected with a foreign key. Im trying to get the data out of the cursussoort table to display in the while loop. cursussoort_ID is showing the id which is obvious but how do i get the 'naam' there out of the cursussoort table?

tables

<?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 } ?>

Upvotes: 1

Views: 73

Answers (3)

krlv
krlv

Reputation: 2380

You should JOIN in your SQL statement to join data from 2 tables:

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

Then you can display it in 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 } ?>

Upvotes: 1

Rishi
Rishi

Reputation: 162

Try Left Outer Join

The main query would be

`SELECT * FROM cursus c LEFT OUTER JOIN cursussoort cr ON (c.cursussoort_ID = cr.id)  WHERE cursussoort_ID = 1 ORDER BY c.begindatum ASC`

Upvotes: 1

Rahul
Rahul

Reputation: 77866

Perform a JOIN between the tables like

SELECT c.* 
FROM cursus c
JOIN  cursussoort cr ON c.ID = cr.cursussoort_ID
WHERE c.cursussoort_ID = 1 
ORDER BY c.begindatum

Upvotes: 1

Related Questions