在其他代码中使用唯一值

Question

Please find the below code for more information.

 // 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>";

The code above is supposed to collect unique values in a column of my table in the database.

This is the output:

ArkCCAPM (where Ark, CCA and PM are the school initials).

$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'];

The code above calculates the total points for the school CCA only. What I want to do is use the unique values (which lists all the schools in my database) and implement that in to the calculation code above so it can work out the totals for not only CCA but all the schools listed in the unique values. So the out put looks like this:

Total CCA: 16
Total PM: 17
Total Ark: 9

Thanks in advance.

Answer 1

Instead of first querying for the list of schools, then using the returned result to get your final result through another query, you may directly use this unique query:

$myQuery = "
  SELECT SUM(result_studpoints) AS total, school
  FROM result
  JOIN students ON result.stud_id = students.stud_id
  GROUP BY school
";

Then use its result like you planned.

SQLfiddle

NOTE: as already pointed by some comments, you have nothing to do with something like $allSchools = mysqli_real_escape_string($row['schools']);: mysqli_real_escape() is used only before writing to the DB, not after reading from it!