Looping through multiple database tables with nested loop?

Question

I have three tables in my database. Students, modules and relations. Each row of the relations table contains a student id and a module id (i.e it contains the modules that a student takes).

I am trying to loop through the relations table and find all the modules that a particular student takes, then print out the details of each module (e.g the name and module code).

I am trying to do this with a nested loop however the inside loop is only running once and printing out the first module. I think this is something to do with having a second sql query inside my while loop and $m_id isnt getting updated with each iteration. Here is my code so far

<?php
        include 'connect.php';
        $sql = "SELECT * FROM relations WHERE student_id = '1'";
        $result = $conn->query($sql);
        if($result->num_rows > 0) {
            while($row = mysqli_fetch_array($result)) {
                $m_id = $row["module_id"];

                $sql = "SELECT * FROM modules WHERE module_id = $m_id";
                $result = $conn->query($sql);
                if($result->num_rows > 0) {
                    while($row = mysqli_fetch_array($result)) {
                        echo $row["module_code"];
                    }
                }
            }
        }
?>

Can anyone help me out with this?

Hint: INNER JOIN

Gordon Linoff
– Gordon Linoff

2017-02-10 12:13:20 +00:00
Commented Feb 10, 2017 at 12:13 — Gordon Linoff
– Gordon Linoff, Commented Feb 10, 2017 at 12:13

Pedro Chiiip · Accepted Answer · 2017-02-10 12:19:32Z

1

You could change your SELECT query in a way that it uses a INNER JOIN to bring all modules from a specific student in one query. Like this:

SELECT * FROM modules
INNER JOIN relations ON relations.module_id = modules.module_id
WHERE relations.student_id = 1;

That way, it would bring all modules which have a relationship with a student of the ID of 1, assuming it's the student you desire to search through its modules. Then you would just iterate through the result array and get the values from modules that you want.

answered Feb 10, 2017 at 12:19

Pedro Chiiip

1882 gold badges2 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

Looping through multiple database tables with nested loop?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related