MYSQL - 内部加入3个表

Question

1. Table of rule_of_exam The field are id_rule, title_exam, id_class, id_subject, date_posting, id_teacher, info, status,time
2. Table of score_multichoice The field are id_score, id_rule, true, false, empty, score, id_student
3. Table of score_essay The field are id_scoressay, id_rule, id_student, score_essay

I joined them with the query

"Select * 
from rule_of_exam 
INNER JOIN score_multichoice ON rule_of_exam.id_rule = score_multichoice.id_rule 
WHERE id_student='$id_student'"

When I joined 2 tables it worked, but when I joined 3 tables it didn't work.

"Select * from rule_of_exam 
INNER JOIN score_multichoice ON rule_of_exam.id_rule = score_multichoice.id_rule
INNER JOIN score_essay ON rule_of_exam.id_rule = score_essay.id_rule
WHERE id_student='$id_student'"

The $id_student is from session student.

I expect the output

title_exam | score_essay | score

but it shows

Warning: Invalid argument supplied for foreach()

Answer 1

The problem is that id_student occurs in more than one table: score_multichoice and score_essay. So the query doesn't know which one to pick. Change your query like this:

SELECT DISTINCT * FROM rule_of_exam 
INNER JOIN score_multichoice ON rule_of_exam.id_rule = score_multichoice.id_rule
INNER JOIN score_essay ON rule_of_exam.id_rule = score_essay.id_rule
WHERE score_essay.id_student = '$id_student'"

In other words: Tell MySQL specifically which table to use, and it could work. Make sure your PHP code can cope with zero result rows.

It would perhaps be better if id_student wasn't present in both table. Always normalize your database.