Displaying information obtained from multiple junction tables with MySQL and PHP

Question

This is for a lesson scheduling app. There is a lessons table (id, start time, end time, etc) which will link via junction tables (via IDs) to a students table (id, first name, last name, etc) and a teachers table (id, first name, last name, etc). These are all many to many relationships. Lessons can have multiple students and students can have multiple lessons. Likewise for teachers.

My question is, what would be the best way to obtain and display the data? I want to be able to list each lesson sorted by date along with the students in the lesson and the teachers for the lesson.

One way would be to use an sql query that will find all the lessons and join all the tables via their junction tables. Then use a loop to spit them all out into a table on the page. The problem I am running into is that the table that gets created lists the same lesson on different lines to account for each student and teacher. This makes a very redundant list when trying to browse by lesson start time or id.

What would be the best way to have each lesson display as one line on in a list with a students column containing all the students and a teachers column containing all the teachers?

Sorry if this is unclear. Thanks for any input and let me know if I need to clarify or add more details.

Table columns:

Student table
studentID firstName lastName email etc

Teacher table
teacherID firstName lastName email etc

Lesson Table
lessonID startTime endTime

Lesson/Student Junc Table
juncID lessonFK StudentFK

Lesson/Teacher Junc Table
juncID lessonFK TeacherFK

Desired output: https://docs.google.com/file/d/0BxmJPAg7gA1AODZKOUg1RXFwMDQ/edit?usp=sharing

UPDATE: Where I'm at. I think I've got it

<?php $lesson_set = find_all_lessons(); ?>
            <table>
                <tr>
                    <td>Start Time</td>
                    <td>Lesson ID</td>
                    <td>Teachers</td>
                    <td>Students</td>

                </tr>

            <?php while($lesson = mysqli_fetch_assoc($lesson_set)) { ?>

                <tr>
                    <td><?php echo htmlentities($lesson["startTime"]); ?></td>
                    <td><?php echo htmlentities($lesson["lessonID"]); ?></td>
                    <td><?php
                        $teachers_in_lesson_set = find_teachers_by_lesson_id($lesson["lessonID"]);

                        while($lesson_teacher = mysqli_fetch_assoc($teachers_in_lesson_set)) {
                            echo htmlentities ($lesson_teacher["firstName"]." ".$lesson_teacher["lastName"].", ");

                        }


                    ?></td>

                    <td><?php 
                        $students_in_lesson_set = find_students_by_lesson_id($lesson["lessonID"]);

                        while($lesson_student = mysqli_fetch_assoc($students_in_lesson_set)) {
                            echo htmlentities ($lesson_student["firstName"]." ".$lesson_student["lastName"].", ");

                        }

                    ?></td>



                </tr>
            <?php } ?>
</table>

The functions

   function find_all_lessons() {
        global $connection;

        $query  = "SELECT * ";
        $query .= "FROM lesson ";
        $query .= "ORDER BY startTime ASC";
        $lesson_set = mysqli_query($connection, $query);
        confirm_query($lesson_set);
        return $lesson_set;
}   


function find_students_by_lesson_id($lesson_id) {
    global $connection;

    $safe_lesson_id = mysqli_real_escape_string($connection, $lesson_id);

    $query = "SELECT * ";
    $query .= "FROM junc_lesson_student ";
    $query .= "JOIN student ON student.studentID = junc_lesson_student.studentFK ";
    $query .= "JOIN lesson ON lesson.lessonID = junc_lesson_student.lessonFK ";
    $query .= "WHERE lessonID = {$safe_lesson_id} ";
    $query .= "ORDER BY firstName ASC ";


    $students_in_lesson_set = mysqli_query($connection, $query);
    confirm_query($students_in_lesson_set);
    return $students_in_lesson_set;

}

The find_teachers_by_lesson_id function is the same as the student one except accessing the teacher tables.

In practice this works but could someone comment on the structure? Is this an acceptable solution or will it cause me problems later on down the road? Thanks again for all your input!

Post a diagram of what you want the table and the output to look like. — Edward, Oct 04 '13 at 15:28
Looks like you are on the right track with database layout, but this is quite a complicated query, take a look at 'inner join' and 'sub' queries. Have ago and then post back what you have tried. Sorry, it just feels like you want us to do this for you. This site is fantastic: http://www.tizag.com/mysqlTutorial/mysqljoins.php — Edward, Oct 04 '13 at 17:46
I've never done anything like this before and I've got to methods in my head that I think would work. Rather than waste time on both I figured I'd see if anyone has already done something like this before to get me in the right direction. I'm going to try and implement some of your post and report back. Thanks for your input! — Guy, Oct 05 '13 at 02:14

Edward · Accepted Answer · 2013-10-04T18:28:03.573

Ok so this should get you into the mindset.

I am using id as a selector for simplification but you could use anything through a POST or GET variable, or just remove the WHERE clause altogether to get all the data.

$query = "SELECT lessonID, startTime
FROM lessonTable 
WHERE id = '$id'";
$lessonResult = mysqli_query($link, $query);

$query = "SELECT firstName 
FROM studentTable 
WHERE id = '$id'";
$studentResult = mysqli_query($link, $query);

$query = "SELECT firstName 
FROM teacherTable 
WHERE id = '$id'";
$teacherResult = mysqli_query($link, $query);

echo "<table>";
while ($row = mysqli_fetch_array($lessonResult)) {
    echo "<tr>"
    echo "<td>{$row['startTime']}</td>";
    echo "<td>{$row['lessonID']}</td>";
    echo "<td>";
    while ($row = mysqli_fetch_array($teacherResult)) {
        $firstNameString = implode(',', $row['firstName']);
        echo $firstNameString;
    }
    echo "</td>";
    echo "<td>";
    while ($row = mysqli_fetch_array($studentResult)) {
        $firstNameString = implode(',', $row['firstName']);
        echo $firstNameString;
    }
    echo "</td>";
    echo "</tr>"
}
echo "</table>";

mysqli_free_result($lessonResult);
mysqli_free_result($teacherResult);
mysqli_free_result($studentResult);

Displaying information obtained from multiple junction tables with MySQL and PHP

1 Answers1