提问人：Goutham Tamil 提问时间：7/20/2023 最后编辑：Byte NinjaGoutham Tamil 更新时间：7/20/2023 访问量：32
如何使用具有最小 php 编码的 where 条件回显不同列的总和值？
How to echo sum values of different column using where condition with minimum php coding?
问：
我创建了学校考试成绩数据库，它由不同的科目分数组成，即化学1，化学2，用于8次考试。我通过提交按钮选择班级部分和主题下拉菜单，成功地在同一页面上呼应了个人分数值。我想在同一页面上显示累积结果，例如通过率、男性、女性的平均值、所有这 8 项考试的总成绩。
我可以通过这些类型的代码来做到这一点
$ab50query_am = "SELECT * FROM test_2324 where (chem1_1 >50 AND chem1_1<81) AND cls='$cls_show' AND sex='M' AND (section='$sect_show' )";
$resab50=mysqli_query($db,$ab50query_am);
$ab50val = mysqli_num_rows($resab50);
为此，我需要为每个类别和考试使用更多编码。是否有任何数组类型的 php 脚本可用于实现此目的..等待帮助
答：
0赞
Jack Mugi
7/20/2023
#1
我认为使用数组、循环和聚合将帮助您实现这一目标。例如，假设您像这样声明/初始化您的数组......
$exams = array('chem1_1', 'chem1_2', 'chem2_1', 'chem2_2', 'chem3_1', 'chem3_2', 'chem4_1', 'chem4_2');
$categories = array('M', 'F', 'Total');
$totals = array_fill_keys($categories, 0);
$counts = array_fill_keys($categories, 0);
然后你也许可以以这种方式循环
$whereCondition = "(cls='$cls_show' AND section='$sect_show') AND (";
foreach ($exams as $index => $exam) {
if ($index !== 0) {
$whereCondition .= " OR ";
}
$whereCondition .= "($exam > 50 AND $exam < 81)";
}
$whereCondition .= ")";
$query = "SELECT sex, COUNT(*) AS count, AVG((chem1_1 + chem1_2 + chem2_1 + chem2_2 + chem3_1 + chem3_2 + chem4_1 + chem4_2) / 8) AS average,
(SUM(chem1_1 > 50 AND chem1_1 < 81) + SUM(chem1_2 > 50 AND chem1_2 < 81) + SUM(chem2_1 > 50 AND chem2_1 < 81) + SUM(chem2_2 > 50 AND chem2_2 < 81) +
SUM(chem3_1 > 50 AND chem3_1 < 81) + SUM(chem3_2 > 50 AND chem3_2 < 81) + SUM(chem4_1 > 50 AND chem4_1 < 81) + SUM(chem4_2 > 50 AND chem4_2 < 81)) AS pass_count
FROM test_2324 WHERE $whereCondition GROUP BY sex";
$result = mysqli_query($db, $query);
while ($row = mysqli_fetch_assoc($result)) {
$category = $row['sex'];
$count = $row['count'];
$passCount = $row['pass_count'];
$totals[$category] += $passCount;
$counts[$category] += $count;
}
$passPercentage = array();
$average = array();
foreach ($categories as $category) {
$passPercentage[$category] = ($totals[$category] / ($counts[$category] * count($exams))) * 100;
$average[$category] = $totals[$category] / $counts[$category];
}
然后，您可以在同一页面上根据需要回显这些值。
评论