PHP loop through multiple database tables

Question

let's say I have a database structure like below:

Students

+--------------+--------------------+-------------------+
| student_id   | student_firstname  | student_lastname  |
+--------------+--------------------+-------------------+
| 1            | John               | Doe               |
+--------------+--------------------+-------------------+
| 2            | Lisa               | Doe               |
+--------------+--------------------+-------------------+
| 3            | Derp               | Doe               |
+--------------+--------------------+-------------------+

Absence

+--------------+--------------------+-------------------+---------------+
| absence_id   | absence_student_id | absence_date      | absence_note  |
+--------------+--------------------+-------------------+---------------+
| 1            | 2                  | 2012-06-10        | A note...     |
+--------------+--------------------+-------------------+---------------+
| 2            | 3                  | 2012-06-30        | Another note. |  
+--------------+--------------------+-------------------+---------------+

And a relationship between column students.student_id and absence.absence_student_id

In PHP I would like to loop through the above tables and get a complete output of the rows in students and if there is a record in absence matching that student, do something to that row and then continue the loop.

My desired HTML output would be something like this:

<table>
 <thead>
  <tr>
    <td>Name:</td>
    <td>Absence date:</td>
    <td>Absence note:</td>
  </tr>
 </thead>

 <tr>
    <td>John Doe</td>
    <td></td>
    <td></td>
 </tr>

 <tr>
    <td>Lisa Doe</td>
    <td>2012-06-10</td>
    <td>A note...</td>
 </tr>

 <tr>
    <td>Derp Doe</td>
    <td>2012-06-30</td>
    <td>Another note.</td>
 </tr>
</table>

I use to loop like this:

<?php
  while ($row = mysql_fetch_array($students_sql_query)) {
   echo "<tr><td>".$row['student_firstname']."</td></tr>";
 }
?>

Is it possible to add some kind of if statement to this? Would I need two different MySQL querys and some kind of foreach loop instead? I'm relatively new to web programming... Thank's in advance and sorry for a long post.

What do you mean? Do you want to exit the loop or change how it is handled? — Ray
– Ray, Commented Jun 19, 2012 at 2:52

Rachael · Accepted Answer · 2012-06-19 02:56:24Z

3

You'll need to join the tables in a query and then get the results from the query.

SELECT Students.student_firstname
   , Students.student_lastname 
   , Absence.absence_date 
   , Absence.absence_note  
FROM Students
LEFT JOIN Absence ON Students.id = Absence.absence_student_id

You should then be able to loop through it similar to this:

<?php
    while ($row = mysql_fetch_array($students_sql_query)) 
    {
?>
        <tr>
            <td>$row['student_firstname']</td>
            <td>$row['student_lastname']</td>
            <td>$row['absence_date']</td>
            <td>$row['absence_note']</td>
        </tr> 
<?
    }
?>

The left join will tell the database to pull in all the students and if they have absences it will populate those too. If the student doesn't have an absence, that will return as null from the database. When it is displayed in the table row in the loop, it should display as an empty column if there is nothing in the absence table for that student.

The only downside is if a student has multiple absences it will duplicate rows in the display table. In that case, you'll have to do some pre-processing either in the code or in the display to handle that condition.

answered Jun 19, 2012 at 2:56

Rachael

4242 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Over a year ago

cool, thanks alot! I will mark this as an answer but I have to wait a few minutes before I do that...

sel · Accepted Answer · 2012-06-19 03:01:08Z

0

Using pdo would be as below:

 $sql = $dbh->prepare('
        SELECT student_firstname, student_lastname, absense_date, absense_note 
        FROM students s inner join absense a 
        ON s.student_id=a.absense_student_id
        ');
        $sql->execute();
        $result = $sql->fetchAll(); <----- fetch All the data
        return $result;

     foreach($result as $row)
            {
            echo '<tr>';
            echo '<td>'.$row['student_firstname'].' '.$row['student_lastname'].'</td>';
            echo '<td>'.$row['absense_date'].'</td>';
            echo '<td>'.$row['absense_note'].'</td>';
            echo '</tr>';
            }

answered Jun 19, 2012 at 3:01

sel

4,9421 gold badge18 silver badges23 bronze badges

Collectives™ on Stack Overflow

PHP loop through multiple database tables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related