我有链接在一起的三个表。我要做的是为班上的所有学生生成成绩单。
表students_info
name sex age students_ID
--------- --- --- -----------
Kinsley M 12 1
Michael m 12 2
Rhianna f 22 3
表scores_panel
1stCA 2ndCA exam students_ID subjectID
----- ----- ---- ----------- ---------
23 15 42 1 1
10 12 7 1 2
43 15 62 1 3
10 12 27 2 1
10 12 57 2 2
23 15 12 2 3
11 12 27 3 1
04 12 57 3 2
13 25 12 3 3
表主题
subjectname subjectID
----------- ---------
english 1
maths 2
biology 3
我希望我的结果看起来像这样：
NAME KINSLEY
SEX M
AGE 12
和报告卡跟随
subject 1stCA 2ndCA EXAM
--------- ----- ----- ----
english 23 15 42
maths 10 12 7
Biology 43 15 62
...所有学生
仅检索一个主题和分数，而不是全部
<?php
include("connect.php");
$generate="SELECT students_info.name, subject.subjectname, scores_panel.1stCA, scores_panel.2ndCA, scores_panel.EXAM
FROM
students_info
LEFT JOIN
scores_panel
ON students_info.students_ID=scores_panel.students_ID
LEFT JOIN
subject
ON
subject.subjectID=scores_panel.subjectID
GROUP BY scores_panel.subjectID ";
$fetch=mysql_query($generate);
while($row=mysql_fetch_array($fetch)or die(mysql_error()))
{
?>
**NAME:**
<?PHP echo $row['name']; ?>
subject 1stCA 2ndCA EXAM
----------
<?PHP echo $row['subjectname']; ?>
<?PHP echo $row['1stCA']; ?>
<?PHP echo $row['2ndCA']; ?>
<?PHP echo $row['EXAM']; ?>
THIS IS YOUR REPORT CARD
<?PHP } ?>
可以，但是每个学生只能显示一个主题，
例
NAME Rhianna
SEX F
AGE 22
和报告卡跟随
subject 1stCA 2ndCA EXAM
--------- ----- ----- ----
english 11 12 27
金斯利
性别
年龄12
和报告卡跟随
subject 1stCA 2ndCA EXAM
--------- ----- ----- ----
english 23 15 42
而不是像这样：
NAME KINSLEY
SEX M
AGE 12
和报告卡跟随
subject 1stCA 2ndCA EXAM
--------- ----- ----- ----
english 23 15 42
maths 10 12 7
Biology 43 15 62
NAME Rhianna
SEX F
AGE 22
和报告卡跟随
subject 1stCA 2ndCA EXAM
--------- ----- ----- ----
english 11 12 27
maths 04 12 57
Biology 13 25 12
...等等，适用于所有学生。
您的帮助将不胜感激
谢谢
最佳答案
你好亲近刚在group by子句中错过了students_info.students_ID
SELECT students_info.name, subject.subjectname, scores_panel.1stCA, scores_panel.2ndCA, scores_panel.EXAM
FROM
students_info
LEFT JOIN
scores_panel
ON students_info.students_ID=scores_panel.students_ID
LEFT JOIN
subject
ON
subject.subjectID=scores_panel.subjectID
GROUP BY students_info.students_ID,scores_panel.subjectID
FIDDLE
关于php - 如何使用Mysql和Php从这三个表为学生生成报告卡，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18102724/