0

Good day. I would like to seek help on this matter:

<?php
$classes = "SELECT * FROM students, lectures, rooms, classes WHERE 
students.student_id=classes.student_id AND 
lectures.lecture_id=classes.lecture_id AND rooms.room_id=classes.room_id";

$class_result = mysqli_query($con, $classes);
?>

This is the table on phymyadmin:

|--class_id--|--student_id--|--lecture_id--|--room_id--|
|     2      |(1)  Goku     |(1) Physics   |    NULL   |
|     3      |(2)Leonardo   |(2) Science   |(1) 101    |
|     5      |(2)Leonardo   |(3)  Math     |(2) 102    |
|------------|--------------|--------------|-----------|
Note: The parenthesis are the IDs

However when I reflect it on html it looks like this:

|--student--|--lecture--|--room--|
|  Leonardo |  Science  |   101  |
|  Leonardo |    Math   |   102  |
|-----------|-----------|--------|

What I want is to be like this:

|--student--|--lecture--|--room--|
|    Goku   |  Physics  |        |
|  Leonardo |  Science  |   101  |
|  Leonardo |    Math   |   102  |
|-----------|-----------|--------|

How can I work it out? I'm using this code:

<table>
  <tr>
    <td>student</td>
    <td>lecture</td>
    <td>room</td>
  </tr>
  <?php while($class = mysqli_fetch_array($class_result)) { ?>
  <tr>
    <td><?=$class['student']?></td>
    <td><?=$class['lecture']?></td>
    <td><?=$class['room']?></td>
  </tr>
  <?php } ?>
</table>
Oween
  • 13
  • 6

1 Answers1

1

You would be better off using the newer JOIN syntax, this allows various combinations of rows existing (JOIN) or not (LEFT/RIGHT JOIN).

$classes = "SELECT * FROM students
              JOIN classes ON students.student_id=classes.student_id
              JOIN lectures ON lectures.lecture_id=classes.lecture_id
              LEFT JOIN rooms ON rooms.room_id=classes.room_id";

Have a read of MySQL: Quick breakdown of the types of joins for more info.

Nigel Ren
  • 56,122
  • 11
  • 43
  • 55
  • This totally works @Nigel Ren. Thank you. But how about let's say for example the room_id is in the middle. |--student--|--room--|--lecture--|? – Oween Feb 01 '18 at 08:58
  • Any table where you may not have a record (if lecture depends on a room record) then you can specify a LEFT JOIN which will return NULL for any columns from a record which does not exist. So you could put a LEFT JOIN on room and lecture. – Nigel Ren Feb 01 '18 at 10:21