Ranking PHP MYSQL Using while loop

Question

I have a problem. I am making a ranking system base on the test scores of applicants. Here is the code:

$testScore = "SELECT Overall_Score, First_Name
                      FROM applicant_details, person, person_details
                      where person.ID_No like person_details.ID_No and Position_Applied = 'Work137' and Person_Type = 'Applicant' and applicant_details.ID_No like person.ID_No";

$result = mysql_query($testScore);

$rank = 0;
$lastScore = false;
$rows = 0;

while( $row = mysql_fetch_array( $result ) ){               

       $name = $row['First_Name'];
       $overall = $row['Overall_Score'];
       $rows++;

       if( $lastScore != $overall){
           $lastScore = $overall;
           $rank = $rows;
        }
        echo"Name:$name rank:$rank score: $overall </br>";
}

The output of this code is whoever is the first one to be queried is number 1. Example output:

Name:Utaha rank:1 score: 85

Name:Rikka rank:2 score: 90

I want to have an output:

Name: Utaha rank: 2 score: 85

Name: Rikka rank 1 score: 90

Fix your query to use proper join syntax and to have the right join conditions between the tables. — Gordon Linoff
– Gordon Linoff, Commented Oct 11, 2015 at 15:58

Ryu · Accepted Answer · 2015-10-11 16:10:58Z

1

just use the ORDER BY [column name to sort] [DESC/ASC] to sort by value...

SELECT Overall_Score, First_Name
                  FROM applicant_details, person, person_details
                  where person.ID_No like person_details.ID_No and Position_Applied = 'Work137' and Person_Type = 'Applicant' and applicant_details.ID_No like person.ID_No ORDER BY Overall_Score DESC

if you want to keep the order, then should use mysql_num_rows() to get the total number of answers, and use that number to rank them:

$rows = mysql_num_rows($result);
while( $row = mysql_fetch_array( $result ) ){               

   $name = $row['First_Name'];
   $overall = $row['Overall_Score'];

   if( $lastScore != $overall){
       $lastScore = $overall;
       $rank = $rows;
    }
    echo"Name:$name rank:$rank score: $overall </br>";
   $rows--;

}

edited Oct 11, 2015 at 16:10

answered Oct 11, 2015 at 16:02

Ryu

1726 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3797088 Over a year ago

Never knew that the solution was very simple. Thank you!

AlVaz · Accepted Answer · 2015-10-11 16:02:58Z

You need an ORDER BY clause in your query:

$testScore = "SELECT Overall_Score, First_Name
              FROM applicant_details, person, person_details
              WHERE person.ID_No like person_details.ID_No
              AND Position_Applied = 'Work137'
              AND Person_Type = 'Applicant'
              AND applicant_details.ID_No like person.ID_No
              ORDER BY Overall_Score DESC
";

As Gordon Linoff alludes to, you can improve this query by using proper JOIN syntax:

$testScore = "SELECT Overall_Score, First_Name
              FROM applicant_details
              JOIN person ON applicant_details.ID_No like person.ID_No
              JOIN person_details ON person.ID_No like person_details.ID_No
              WHERE Position_Applied = 'Work137'
              AND Person_Type = 'Applicant'
              ORDER BY Overall_Score DESC
";

Collectives™ on Stack Overflow

Ranking PHP MYSQL Using while loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related