PHP Mysql joinget results

Question

I have 3 tables the subject, student, and relation
The following are sample data

students table
id    name
1     John
2     Doe
3     Jane

subject table
id    subject
1     Math
2     Science
3     English

relation table
id    student_id    subject_id
1       1                1
2       1                3
3       2                1
4       1                2

in this case sudent with id=1 has 3 subjects. How can I get result like this?

student name is John
subjects are:
Math
Science
English

I already get the values for student, what I'm trying to do is get his subject to be shown by user. I'm totally confused using join tables. I'm a newbie in php and I start learning this last week. I'm getting an error on my code. Please help.
My Cuurent code is this:

<?php
//mysql connection

$query = "
  SELECT *,* FROM relation, subject
  on subject_id = id
  WHERE student_id = $student_id
";
$result = mysql_query($query);
?>
Student name is <?php echo $name ?><br />
Subjects are:<br />
<?php
while($row = mysql_fetch_array($result)) {
  echo $row["subject"];
}
?>

Answer 1

The simple way to do this, is to query all students:

SELECT * FROM `students`;

and then loop the resultset in php, now for each of the rows you have to make another query to get students subjects:

SELECT * FROM `subject` WHERE `id` IN (SELECT subject_id FROM relation WHERE student_id={$student_row['id']})

assuming $student_row is the name you choose for assigning fetched record.

Now, you do a nested-loop to fetch this query, and print students' subjects.

Answer 2

Start by making sure your query is right. I think this is what you're looking for:

SELECT subjects.* 
FROM subjects, 
INNER JOIN relations ON relations.subject_id = subjects.id
WHERE relations.student_id = $student_id

It might pay to check it in a mysql client first (e.g., phpMyAdmin). You'll notice in the SELECT part I only have subjects.* because that is all you need.

Answer 3

I actually ask an interview question to weed out people who don't have basic SQL knowledge but claim it on their resume that this is a direct implementation of. It can lead to a good conversation about database design, normalization, de-normalization etc.

What I'd want to see is the basic join syntax that goes hand in hand with the relationship table you've created.

"Select subject from subjects inner join relations on relations.subject_id = subjects.id where relations.student_id = ?".

Taking it the next step you get to

"Select subject from subjects inner join relations on relations.subject_id = subjects.id inner join students on relations.student_id = students.student_id where students.name = ?".

allowing you to query without pre-fetching the student id if you don't want to expose your internal database keys to the external user.

The question mark is for variable binding, its rare that its a reasonable practice to not escape inputs for database interactions. Variable binding for queries is an easy way to get good coverage on that issue.

I love asking this design problem as an interview question because it can quickly lead to discussions about SQL injection, how to sanitize web form input, and general best practices in data management, web application development and database design.

Answer 4

There's the GROUP_CONCAT() aggregate function for this situation:

SELECT students.id, students.name, GROUP_CONCAT(subject.subject) AS subjects
FROM students
LEFT JOIN relation ON students.id = relation.student_id
LEFT JOIN subject ON relation.subject_id = subject.id
GROUP BY  students.id
ORDER BY students.name

which will return data like this:

+-------------+---------------+------------------------+
| students.id | students.name | subject.name           |
+-------------+---------------+------------------------+
| 1           | John          | Math, English, Science |
| 2           | Doe           | Math                   |
| 3           | Jane          | NULL                   |
+-------------+---------------+------------------------+

You can do this without the grouping, in which case you'll get one row for every id/student name/subject name, and then you can use a simple state machine like in OMG Ponies' answer for the display portion.