如何用Mysql和Php从这三张表生成学生成绩单 [英] How to Generate a Report Card for Students from This Three Table with Mysql and Php
问题描述
我有三个链接在一起的表.我想要做的是为一个班级的所有学生生成一个成绩单.
I have three tables that link together. What I want to do is to generate a report card for all the students in a class.
表格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
我希望我的结果是这样的:
I want my result to look like this:
NAME KINSLEY
SEX M
AGE 12
并附上成绩单
subject 1stCA 2ndCA EXAM
--------- ----- ----- ----
english 23 15 42
maths 10 12 7
Biology 43 15 62
...等所有学生
只检索一门科目和分数，而不是全部
only one subject and scores retrieved instead of all
<?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 } ?>
它有效，但每个学生只显示一个主题，
It works, but displays only one subject for each student,
示例
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
... 等等所有学生.
... and so on for all students.
您的帮助将不胜感激
谢谢
推荐答案
在获取和显示数据的过程中存在一些缺陷.似乎主要缺陷是限制性的GROUP BY"语句.有多种解决方案，但我将展示一个简单的解决方案，它只需要执行一个查询.
There are some flaws in the process of acquiring and showing up data. It seems that the main flaw is the limiting 'GROUP BY'-statement. There are multiple solutions, but I'll show a simple one which only requires one query to be executed.
实际上，您的过程由两个循环组成:一个循环是您获取学生，另一个是您想要结果.它是这样的:
In fact your process consists of two loops: one loop in which you are acquiring students and one in which you want the results. It goes as follows:
<?php
include("connect.php");
$generate="SELECT students_info.students_ID, 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
ORDER BY students_info.name ASC
";
$fetch=mysql_query($generate);
$previousId = -1;
while($row=mysql_fetch_array($fetch)or die(mysql_error()))
{
if($row['students_ID'] != $previousId)
{
//New student: show name [and other info]
echo "**" . $row['name'] . "**";
echo "subject 1stCA 2ndCA EXAM";
echo "------------------------";
$previousId = $row['students_ID'];
}
?>
<?PHP echo $row['subjectname']; ?>
<?PHP echo $row['1stCA']; ?>
<?PHP echo $row['2ndCA']; ?>
<?PHP echo $row['EXAM']; ?>
THIS IS YOUR REPORT CARD
<?php
}
?>
发生了什么?SQL 输出的行都包含学生 ID 和他们的个性.它只需要显示一次(与表头一起)，也就是当一个新学生出现在输出中时.变量 $previousId 用于此目的.然后所有数据都显示在那个成绩表中.
What's happening? The rows of your SQL-output all contain a student id and their personalia. It has only to be shown once (together with the table header), that is when a new student shows up in the output. The variable $previousId is used for that. Then all data is shown in that table of grades.
如果您熟悉 phpMyAdmin，您可以随时测试您的 SQL 代码，看看它会产生什么.
If you are familiar with phpMyAdmin, you can always test your SQL-code to see what it will produce.
祝你好运！
这篇关于如何用Mysql和Php从这三张表生成学生成绩单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
