$check_res = $con->query(
"SELECT *
FROM `results`
WHERE school_session='$session'
AND Term='$term'
AND class='$class'
AND subjectID='$subjectID'
ORDER BY Average DESC ");
$score_ends = array(1 => "st", 2 => "nd", 3 => "rd", 4 => "th");
$key_counter=1;
foreach ($check_res as $value) {
$key_counters=$key_counter++;
if($RegNum == $value['StudentReg']){
if($key_counters < 4){
echo $key_counters.$score_ends[$key_counters];
}else{
echo $key_counters.$score_ends[4];
}
}
}
this code up here ranks users based on average. Its works fine on that. But it does not assign same rank to users with same average score. E.g 20-1st 20-2nd
but what i want is this 20-1st 20-1st 21-3rd
Advertisement
Answer
Talking points
So there are a few things with your code that aren’t best practice or are a bit confusing.
- You should really use
preparedstatements with you SQL queries. They offer far better protection when using user derived data (it isn’t clear whether your search parameters are from user input or from other code) - If you’re using
$variablesin your double quoted strings it’s good practice to use{curly braces}to encompass them. It solves a variety of issues withPHPusing the correct variable and also makes it easier to read and maintain - As others have said you haven’t actually shown where
$Reghas come from – so I have ignored it in the following code
Solution
Table/Data
Assuming that you have the following data in your table…
Table: results
StudentReg | school_session | Term | class | subjectID | Average -----------+---------------+------+-------+-----------+------------- 1 | 1022 | 3 | 37 | 66 | 121 2 | 1022 | 3 | 37 | 66 | 115 3 | 1022 | 3 | 37 | 66 | 129 4 | 1022 | 3 | 37 | 66 | 121 5 | 1022 | 3 | 37 | 66 | 121 6 | 1022 | 3 | 37 | 66 | 117 7 | 1022 | 3 | 37 | 66 | 129 8 | 1022 | 3 | 37 | 66 | 125 9 | 1022 | 3 | 37 | 66 | 120 10 | 1022 | 3 | 37 | 66 | 118 11 | 1022 | 3 | 37 | 66 | 125 12 | 1022 | 3 | 37 | 66 | 124 13 | 1022 | 3 | 37 | 66 | 125 14 | 1022 | 3 | 37 | 66 | 125
Sorted data returned by query
StudentReg | school_session | Term | class | subjectID | Average -----------+---------------+------+-------+-----------+------------- 2 | 1022 | 3 | 37 | 66 | 115 6 | 1022 | 3 | 37 | 66 | 117 10 | 1022 | 3 | 37 | 66 | 118 9 | 1022 | 3 | 37 | 66 | 120 1 | 1022 | 3 | 37 | 66 | 121 5 | 1022 | 3 | 37 | 66 | 121 4 | 1022 | 3 | 37 | 66 | 121 8 | 1022 | 3 | 37 | 66 | 125 12 | 1022 | 3 | 37 | 66 | 124 11 | 1022 | 3 | 37 | 66 | 125 13 | 1022 | 3 | 37 | 66 | 125 14 | 1022 | 3 | 37 | 66 | 125 3 | 1022 | 3 | 37 | 66 | 129 7 | 1022 | 3 | 37 | 66 | 129
Code
I have adapted the code to use prepared statements.
variables
Presumably these are declared somewhere further up your code…
$session = 1022; $term = 3; $class = 37; $subjectID = 66;
mysqli
// SQL query to SELECT "Average" and "StudentReg" from "results"
// ?s are a place holder for values we'll bind later
$sql = "
SELECT StudentReg, Average
FROM `results`
WHERE school_session = ?
AND Term = ?
AND class = ?
AND subjectID = ?
ORDER BY Average DESC
";
$query = $mysqli->prepare(); // Prepare the query
$query->bind_param("iiii", $session, $term, $class, $subjectID); // Bind search values to parameters ("iiii" : one "i" for each variable, set's data type to "int")
$query->execute(); // Run the query
$query->Store_result(); // Store the result set
$query->bind_result($studentreg, $average); // Bind returned rows to variables
$score_ends = array(1 => "st", 2 => "nd", 3 => "rd", 4 => "th");
$last_value = false; // Initialise last value of result set
### $counter = 0; // Initialise the counter
$rank = 0;
while($query->fetch()){ // Loop through the result set
### $counter++; // Increment the counter
### // "if" statement using ternary logic
### // Sets the rank as either the counter OR the previous rank if it's the same
### $rank = ($last_value == false || $last_value <> $average) ? $counter : $rank;
// "if" statement using ternary logic
// Pre-increments (++ before variable) rank if the Average is not the same as previous
// ...or leaves as is if it is the same
$rank = ($last_value == false || $last_value <> $average) ? ++$rank: $rank;
$end = ($rank > 3) ? $score_ends[4] : $score_ends[$rank]; // Sets the suffix to the rank using ternary logic
echo "{$rank}{$end} {$studentreg}<br>"; // Prints the result; e.g. 1st 2 {2 == the StudentReg, presumably you'll update that to be a name or something}
$last_value = $average; // Sets current average as last value ready for next loop
}
PDO
// SQL query to SELECT "Average" and "StudentReg" from "results"
// ?s are a place holder for values we'll bind later
$sql = "
SELECT StudentReg, Average
FROM `results`
WHERE school_session = ?
AND Term = ?
AND class = ?
AND subjectID = ?
ORDER BY Average DESC
";
$query = $PDO->prepare(); // Prepare the query
$query->execute([$session, $term, $class, $subjectID]); // Execute the query and bind variables to place holders
$score_ends = array(1 => "st", 2 => "nd", 3 => "rd", 4 => "th");
$last_value = false; // Initialise last value of result set
### $counter = 0; // Initialise the counter
$rank = 0;
while($query->fetch()){ // Loop through the result set
### $counter++; // Increment the counter
### // "if" statement using ternary logic
### // Sets the rank as either the counter OR the previous rank if it's the same
### $rank = ($last_value == false || $last_value <> $row["Average"]) ? $counter : $rank;
// "if" statement using ternary logic
// Pre-increments (++ before variable) rank if the Average is not the same as previous
// ...or leaves as is if it is the same
$rank = ($last_value == false || $last_value <> $row["Average"]) ? ++$rank: $rank;
$end = ($rank > 3) ? $score_ends[4] : $score_ends[$rank] ; // Sets the suffix to the rank using ternary logic
echo "{$rank}{$end} {$row["StudentReg"]}<br>"; // Prints the result; e.g. 1st 2 {2 == the StudentReg, presumably you'll update that to be a name or something}
$last_value = $row["Average"]; // Sets current average as last value ready for next loop
}
Output
1st 2 2nd 6 3rd 10 4th 9 5th 1 5th 5 5th 4 8th 8 9th 12 10th 11 10th 13 10th 14 13th 3 13th 7