sql - How do I assign same rank to users with the same score with PHP?

View Original

Solution:

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 prepared statements 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 $variables in your double quoted strings it's good practice to use {curly braces} to encompass them. It solves a variety of issues with PHP using the correct variable and also makes it easier to read and maintain
As others have said you haven't actually shown where $Reg has 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

Sorted data returned by query

{-code-7}

Code

I have adapted the code to use prepared statements.

variables

Presumably these are declared somewhere further up your code...

{-code-9}

mysqli

{-code-10}

PDO

{-code-11}

Output

{-code-12}

Answer

-----+------+-------+

Answer

--- 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|||StudentReg | school_session | Term | class | subjectID | Average

Answer

-----+------+-------+

Answer

--- 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|||prepared|||$session = 1022; $term = 3; $class = 37; $subjectID = 66;|||// 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 }|||// 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 }|||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

Answer

Solution:

If you are running MySQL 8.0, you can do the ranking directly in the database, using window functions:

select r.*, rank() over(order by average desc) rn 
from results r
where 
    school_session = ?
    and term = ?
    and class = ?
    and  subjectid = ?
order by average desc

rank() assigns the same number to ties.

Note that I changed the query to use parameters rather than concatenating php variables in the query string: this is both safer and more efficient.

Source