How to make ranking system in PHP

Question

I have problem. . In my case i make championship. . You know, the winner is determined by the highest value. . Example :

$sql = "SELECT bla bla bla FROM `user` ORDER BY `point` DESC";
$result = mysql_query($sql);
$i=0;
while($row = mysql_fetch_array($result)) {
    $1++;
    echo "rank ".$i." is ".$row['name']." with point ".$row['point'].";
}

It's will show

rank 1 is abc with point 10
rank 2 is def with point 9
rank 3 is ghi with point 8
rank 4 is jkl with point 7
rank 5 is mno with point 7
rank 6 is pqr with point 3
rank 7 is stu with point 1

The question. .

look the result. . rank 4 and rank 5 have same point. . how to make they in same possition ??
how to auto detect title of champions like rank 1 is the big boss, rank 2 is a boss, rank 7 is worker ??
how to tell ex: you in possition 6 ??

For your first question,*hint:* just don't increment `$i` if point is same as the previous point. — Rikesh, May 24 '13 at 04:15
@Rikesh: usually when multiple players have same scores - they take the same place but place is incremented. Like A and B are on the first place, and C is on the 3rd place. — zerkms, May 24 '13 at 04:16

score 6 · Answer 1 · answered May 24 '13 at 04:38

$sql = "SELECT bla bla bla FROM `user` ORDER BY `point` DESC";
$result = mysql_query($sql);

if( !$result ){
  echo 'SQL Query Failed';
}else{

  $rank = 0;
  $last_score = false;
  $rows = 0;

  while( $row = mysql_fetch_array( $result ) ){
    $rows++;
    if( $last_score!= $row['point'] ){
      $last_score = $row['point'];
      $rank = $rows;
    }
    echo "rank ".$rank." is ".$row['name']." with point ".$row['point'].";
  }
}

This code will also adjust the rankings properly - such as:

rank 1 is Adam Aarons with point 100
rank 2 is Barry Blue with point 90
rank 2 is Betty Boop with point 90
rank 4 is Charlie Chaplin with point 80

Note that there is no "rank 3", as "Charlie Chaplin" is actually the fourth-highest scorer.

If you do not want this behaviour, then simply replace $rank = $rows; with $rank++;

cerkiewny · Answer 2 · 2013-05-24T06:57:23.603

1

ad 1. group by point, add @curRow := \@curRow + 1 AS row_numbe to select the current row number which will be the rank position

ad 2. left join title_dict on row_number = title_dict.id

where title dictionary is table with title_id and title name dictionary

ad 3. select from your select on id = your id

edited May 24 '13 at 06:57

answered May 24 '13 at 04:19

cerkiewny

2,761
18
36

`ROW_NUMBER()` --- OP asked about mysql, not oracle – zerkms May 24 '13 at 04:33
I meant usage of variable storing the current row number added to the statement \@curRow := \@curRow + 1 AS row_number like this, sorry for this stupid comment that didn't explain anything. – cerkiewny May 24 '13 at 05:14
so put it in the answer. OP has no idea what you're talking about – zerkms May 24 '13 at 05:17
Like this dude. . SELECT l.user_id, l.name, l.point, \@curRow := \@curRow + 1 AS rank FROM user l LEFT JOIN level ON rank = level_id JOIN (SELECT \@curRow := 0) r ORDER BY point DESC but this is error unknown column rank in joining table if not join with table level it's work thank's – Dj Uckie May 27 '13 at 05:39

score 1 · Answer 3 · answered Apr 27 '20 at 17:46

1

Actually, a very easy way to order players with same amount of points is to add a column "points_last_update" containing the timestamp of the last update to the "points" column. Then you just add the "points_last_update" to the ORDER BY in your sql statement. That way, the player who has reached the points the first gets the higher ranking, wich is logic.

answered Apr 27 '20 at 17:46

Whisper

31
2

If the score table is insert only and has a auto incrementing primary key, then the primary key maybe used in a similar way as an explicit time stamp column. However I agree and explicit column is better. – bishop Apr 27 '20 at 17:52

score 0 · Answer 4 · answered May 24 '13 at 04:37

Save the last score and make sure it does not equal the current score before you increment.

$sql = "SELECT bla bla bla FROM `user` ORDER BY `point` DESC";
$result = mysql_query($sql);
$i=0;
$lastscore = NULL;
while($row = mysql_fetch_array($result)) {
   if ($lastscore != $row['point']){
   $i++;
   }
   echo "rank ".$i." is ".$row['name']." with point ".$row['point'];
   $lastscore = $row['point'];
}

How to make ranking system in PHP

4 Answers4