php foreach出错了

Question

我的数据库中有一个名为 tbl_grade 的表

id  |  studentid  | compid | subcompid | recordid | termid | grade
1        101          1          1           1         1       95
2        101          1          2           1         1       80
3        102          1          1           1         1       90
4        102          1          2           1         1       95
5        103          2          2           1         1       90

我希望像这样在我的 View 中打印它

studentid | grade in subcomp1 | grade in subcomp2 | total (of both grades)
   101              95                 80                  175
   102              90                 95                  185

为了回应我的观点，我有这个

<?php foreach ($this->UserModel->grades() as $grade): ?>
  <?php if($grade->compid=='1' && $grade->subcompid==$subcomp->id && $grade->recordid==$subcomp->recordid && $grade->studentid==$student->studentid && $grade->termid == '1'){ ?>
     <td class="text-center"><?php echo $grade->grade; ?></td>
  <?php }
endforeach; ?>

有了这个，我得到了我想要的，并且能够根据学生打印成绩，但是我在回应总数时遇到了问题。我有这个代码

<?php foreach ($this->UserModel->grades() as $grade): ?>
   <?php if ($grade->compid=='1' && $grade->studentid==$student->studentid && $grade->subcompid==$subcomp->id && $grade->recordid==$subcomp->recordid && $grade->termid == '1') { ?>
      <td class="colScore text-center"><strong>
         <?php 
         $mySum = 0;
           foreach ($this->UserModel->gradeSum($student->studentid) as $sum):
             if($sum->compid==1 && $sum->termid=='1'){
               $mySum = $mySum + $sum->grade;
             }
           endforeach; echo $mySum;?></strong>
      </td>
   <?php } 
endforeach; ?>

php 标签内的代码有效，我可以获得正确的成绩总和。然而，它的输出方式是这样的

studentid | grade in subcomp1 | grade in subcomp2 | total (of both grades) |
   101    |         95        |        175        |          80            |  175
   102    |         90        |        185        |          95            |  185

它打印每个年级的总数。总数应该刚好在最后。我的 foreach 循环可能有问题？我应该把它放在哪里？我已经盯着它看了好几个小时了 z_z

*假设所有的值都是从数据库中正确获取的

提前致谢!

Answer 1

您实际上可以使用数据透视查询在 MySQL 中处理此问题:

SELECT studentid,
       MAX(CASE WHEN subcompid = 1 THEN grade END) AS grade_in_subcomp1,
       MAX(CASE WHEN subcompid = 2 THEN grade END) AS grade_in_subcomp2,
       SUM(grade) AS total
FROM tbl_grade
GROUP BY studentid