请找到下面的代码以获取更多信息。
// Finding a list of schools.
$schoolQuery = "SELECT DISTINCT stud_school AS schools FROM students";
$schoolsList = mysqli_query($conn, $schoolQuery);
while ($row = mysqli_fetch_assoc($schoolsList)) {
echo $row['schools'];
}
echo "<br>";
上面的代码应该在数据库中我的表的一列中收集唯一值。
这是输出:
ArkCCAPM(其中Ark,CCA和PM是学校的首字母缩写)。
$allSchools = mysqli_real_escape_string($row['schools']);
// Sums upp all the points recieved by each student for specific school
$myQuery = "SELECT SUM(result_studpoints) AS total FROM result WHERE stud_id IN (SELECT stud_id FROM students WHERE stud_school = 'CCA')";
$result = mysqli_query($conn, $myQuery);
$data = mysqli_fetch_assoc($result);
echo "Total CCA: ".$data['total'];
上面的代码仅计算学校CCA的总分。我想做的是使用唯一值(它列出了我数据库中的所有学校)并将其实现到上面的计算代码中,以便它不仅可以计算出 CCA 的总数,还可以计算出唯一值中列出的所有学校的总数。所以输出看起来像这样:
Total CCA: 16
Total PM: 17
Total Ark: 9
提前谢谢。
与其先查询学校列表,然后使用返回的结果通过另一个查询获取最终结果,不如直接使用此唯一查询:
$myQuery = "
SELECT SUM(result_studpoints) AS total, school
FROM result
JOIN students ON result.stud_id = students.stud_id
GROUP BY school
";
然后按照您的计划使用其结果。
SQLfiddle
注意:正如一些评论已经指出的那样,您与类似 $allSchools = mysqli_real_escape_string($row['schools']);
:mysqli_real_escape()
仅在写入数据库之前使用,而不是在读取数据库之后使用!
$allSchools = mysqli_real_escape_string($row['schools']);
// Sums upp all the points recieved by each student for specific school
$myQuery = "SELECT students.stud_school, SUM(result_studpoints) AS total FROM result JOIN students ON students.stud_id = result.stud_id GROUP BY students.stud_school";
$result = mysqli_query($conn, $myQuery);
$data = mysqli_fetch_assoc($result);
echo "Total " . $data['stud_school'] . ": ".$data['total'] . "<br />";
试试这个:
// Finding a list of schools.
$schoolQuery = "SELECT DISTINCT stud_school AS schools FROM students";
$schoolsList = mysqli_query($conn, $schoolQuery);
while ($row = mysqli_fetch_assoc($schoolsList))
{
$allSchools = mysqli_real_escape_string($conn, $row['schools']);
// Sums upp all the points recieved by each student for specific school
$myQuery = "SELECT SUM(result_studpoints) AS total FROM result WHERE stud_id IN (SELECT stud_id FROM students WHERE stud_school = '$allSchools')";
$result = mysqli_query($conn, $myQuery);
$data = mysqli_fetch_assoc($result);
echo "Schools: ".$row['schools']." --> Total CCA: ".$data['total'];
echo "<br>";
}