Sort exam results by highest to lowest

Question

I've made a simple PHP based exam for my students. The results are recorded as "q1", "q2", "q3", "q4" and "q5" in the database.

Correct answers are recorded as "yes" and incorrect answers as "no".

There are 5 questions and I want the total score to be 100.

All the functions work as I'd like them to, but the problem is, I have no idea how to sort the results from high to low. Any help is highly appreciated!

$result = mysqli_query($db,"SELECT * FROM table");
while($row = mysqli_fetch_array($result)){
    $score = 0;
    echo '<img alt="'.$row["name"].'" src="images/'.$row["name"].'.jpg"         width="118" height="158"';
    if ($row["q1"] === "yes") {$score=$score+20;}
    if ($row["q2"] === "yes") {$score=$score+20;}
    if ($row["q3"] === "yes") {$score=$score+20;}
    if ($row["q4"] === "yes") {$score=$score+20;}
    if ($row["q5"] === "yes") {$score=$score+20;}
    echo ' /> '.$row["name"] . ' ' . $score;
}

can you include an snippet of how the data appears and how you want it to be displayed? — mcv, Mar 22 '18 at 14:34
You can store each score into an array, and then sort it with the function arsort: http://php.net/manual/es/function.arsort.php — Zander, Mar 22 '18 at 14:34
Either sort them in the query, or else you'll have to load them all into a big array and use `usort()` or `array_multisort()` to sort the array. If the number of students is large, then sorting in an array will be heavy on memory. Also, sorting using the query could be slow if you have a lot of records, unless you store the final score value on the record and make an index on it. — Simba, Mar 22 '18 at 14:39

ScaisEdge · Accepted Answer · 2018-03-22T14:54:35.810

6

Instead of the while and the inner if, you could do all with the select and order by the result column eg:

  select name, if(q1='yes', 20, 0) +
          if(q2='yes', 20, 0) +
          if(q3='yes', 20, 0) +
          if(q4='yes', 20, 0) +
          if(q5='yes', 20, 0) result  
  from  table 
  order by result desc

edited Mar 22 '18 at 14:54

answered Mar 22 '18 at 14:37

ScaisEdge

131,976
10
91
107

Exactly what I needed! I only added " desc" to the last line and it worked perfectly! Thank you so much! – Claudio Delgado Mar 22 '18 at 14:54

Rantanen · Answer 2 · 2018-03-22T15:05:36.907

$result = mysqli_query($db,"SELECT * FROM table");
//I sugges you to first store the information into an array:
$scores = [];

while($row = mysqli_fetch_array($result)){
  $score = 0;
  //Calculate the score
  if ($row["q1"] === "yes") {$score=$score+20;}
  if ($row["q2"] === "yes") {$score=$score+20;}
  if ($row["q3"] === "yes") {$score=$score+20;}
  if ($row["q4"] === "yes") {$score=$score+20;}
  if ($row["q5"] === "yes") {$score=$score+20;}
  //Push it into the array
  array_push($scores,   array("name"   => $row["name"],
                              "image"  => 'src="images/'.$row["name"].'.jpg"',
                              "score"  => $score
                            ));

  }

Sort the array: (Sort Multi-dimensional Array by Value)

  usort($scores, function($a, $b) {
      return $b['score'] - $a['score'];
  });


  //Do as you wish with the array

  print_r($scores);

Sort exam results by highest to lowest

2 Answers2