SQL Statement JOIN for students and class

Question

I am trying to create a page where students can view the homework set by teacher for their classes. With the query I have now, it is not giving me any errors, but it's not returning the right classes. Instead it appears to show the latest that have been added. Here is my SQL statement.

What I need is a statement which selects from each class that the student that is logged in is associated with.

$id= $_SESSION['id'];

 mysqli_query($conn, "SELECT * FROM class INNER JOIN student_class ON class.class_id=student_class.class_ID INNER JOIN student ON student.ID= student_class.class_ID WHERE tbluser_ID='$id' ");

Here is how I want the information to be displayed

        <table width='80%' border=0>
        <tr bgcolor='#CCCCCC'>
            <td>ID</td>
            <td>Class Name</td>
            <td>Manage </td>
        </tr>
        <?php 
        //while($res = mysql_fetch_array($result)) 
        while($res = mysqli_fetch_array($result)) {         
            echo "<tr>";
            echo "<td>".$res['class_id']."</td>";
            echo "<td>".$res['classname']."</td>";

And here is my database

GMB · Accepted Answer · 2020-01-06T20:40:34.060

The join condition that brings in table student does not seem right.

I think that this:

ON student.ID = student_class.class_ID

Should be:

ON student.ID = student_class.student_ID

New query:

SELECT * 
FROM class c 
INNER JOIN student_class sc ON sc.class_id = c.class_id
INNER JOIN student s ON s.id = sc.student_id
WHERE s.tbluser_ID = :id

Side note:

you do want to use prepared statements instead of concatenating variable into your query string; you can have a look at this post for the whys and hows
table aliases usually make queries easier to read and maintain
generally you want to avoid SELECT *; it is better to enumerate (and alias if needed) the columns that you want your query to return

@omar Please note that GMB has used PDO's prepared statement functionality here. You should too in your code. — Dharman, Jan 06 '20 at 20:39

SQL Statement JOIN for students and class

1 Answers1