php - 显示一个包含多列的表，每列的条件不同 PHP MySQL

Question

我在学生表中有这个数据:

stu_id  stu_name   stu_exam   stu_exam_year  stu_mark  stu_grade  stu_est_mark  stu_est_grade
111     Alice      First      2015           80        A          
111     Alice      Mid        2015           75        B
111     Alice      Final      2015           65        B

222     Frankline  First      2015           75        B
222     Frankline  Mid        2015           60        B
222     Frankline  Final      2015           50        C

我想以表格形式输出数据，如下所示，其中估计成绩=每门考试的总分/满分总分* 100

No    ID    Name       First-term Exam     Mid-term Exam       Final Exam         Estimated
                       Mark   |  Grade     Mark   |  Grade   Mark   |  Grade    Mark   |  Grade
1     111   Alice      80         A        75        B       65         B       73      B
2     222   Frankline  75         B        60        B       50         C       62      B

我尝试使用这个 SELECT 语句

<?php
$no = 0;
query = "SELECT * FROM student GROUP BY stu_id;"

$result_set =  mysqli_query($con, $query);

while( $row = mysqli_fetch_array($result_set)){

$no++;
?>

并在下面应用一些条件

<td><?php echo $no; ?></td>                        
<td><?php echo $row['stu_name']; ?></td>

<!--This is for First-term -->
<?php if(($row['stu_exam'] == "First"){ ?>
<td><center><?php echo $row['stu_mark']; ?></center></td>
<td><center><?php echo $row['stu_grade']; ?></center></td>
<?php }
else {?>
<td><center></center></td>
<td><center></center></td>
<?php } ?>

<!--This is for Mid-term -->
<?php if(($row['stu_exam'] == "Mid"){ ?>
<td><center><?php echo $row['stu_mark']; ?></center></td>
<td><center><?php echo $row['stu_grade']; ?></center></td>
<?php }
else {?>
<td><center></center></td>
<td><center></center></td>
<?php } ?>

<!--This is for Final -->
<?php if(($row['stu_exam'] == "Final"){ ?>
<td><center><?php echo $row['stu_mark']; ?></center></td>
<td><center><?php echo $row['stu_grade']; ?></center></td>
<?php }
else {?>
<td><center></center></td>
<td><center></center></td>
<?php } ?>

但查询只是读取每个stu_id表中行的第一个列表，并输出如下

No    ID    Name       First-term Exam     Mid-term Exam       Final Exam         Estimated
                       Mark   |  Grade     Mark   |  Grade   Mark   |  Grade    Mark   |  Grade
1     111   Alice      80         A        
2     222   Frankline  75         B    

为什么？我想计算估计分数(平均)，其中每门考试的所有分数之和除以所有已参加考试的总分数。如何生成此值并将生成的值更新到 stu_est_mark 和 stu_est_grade 属性的学生表中。

Answer 1

可以做的一件事是从数据库中获取所有数据，然后在您的 php 代码中根据学生 ID 对标记进行分类。像这样的东西:

$q1=mysqli_query($con,"SELECT DISTINCT(stud_id) FROM student");
while($row=mysqli_fetch_assoc($q1))
{
   $q2=mysqli_query($con,"SELECT * FROM student where stud_id=".$row['stud_id']);
   while($r=mysqli_fetch_assoc($q2))
   {
       //your code
   }
}