Jump to content


Photo

Determine rank within series of numbers


  • Please log in to reply
3 replies to this topic

#1 bartibus

bartibus
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 31 March 2006 - 08:43 AM

Hi,

Does anyone know how I can make a rank list from a series of numbers? I have a competition in which participants can gather points; in a mysql database table there's a row for each participant (users) and a column in which the point totals are saved (totals). When I use 'ORDER BY' to sort the users according to their totals, the rank is consecutive, even when two or more users have the same amount of points. Is it possible to make a script that sorts the users according to their totals and assigns a rank number, that remains the same if two or more users have the same total (and skips the relevant rank numbers)? Below is an example of what I would like to have:

1 User A 200
2 User B 150
3 User C 125
3 User D 125
3 User E 125
6 User F 100

I hope that someone can help me with this,
Thanks in advance,
Bart

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 31 March 2006 - 11:40 AM

try

<?php
$scores = array (
    'User A' => 200,
    'User B' => 150,
    'User C' => 125,
    'User D' => 125,
    'User E' => 125,
    'User F' => 100
);

$rank = 1;
$prev = 0;
$count = 0;
foreach ($scores as $name => $score) {
         if ($score==$prev) {
             ++$count;
         }
         else {
              $rank = ++$count;
         }
         echo "$rank $name $score<br/>";
         $prev = $score;
}
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 bartibus

bartibus
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 03 April 2006 - 10:31 AM

Thanks,
The problem is though that the scores are variable; the user A, etc. was just an example. I have tried to change the code so that it will retrieve the users and scores from the database, but the resulting list gives every user the same rank. This is my code:
<?
$query = "SELECT username, name, totaal FROM punten ORDER BY totaal DESC, name ASC";
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_assoc ($result)) {
$username= $row['username'];
$name= $row['name'];
$totaal= $row['totaal'];

$scores = array($name => $totaal);
$rank = 1;
$prev = 0;
$count = 0;
foreach ($scores as $name => $totaal) {
if ($totaal==$prev) {
++$count;
}
else {
$rank = ++$count;
}
echo "$rank $name $totaal<br />";
$prev = $totaal;
}
}
?>

Do you know what is wrong with my code?

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 03 April 2006 - 07:36 PM

If you are doing it straight from the db table, sorted, you can drop the array code.

$rank = 1;
$prev = 0;
$count = 0;
while ($row = mysql_fetch_assoc ($result)) {
    $username= $row['username'];
    $name= $row['name'];
    $totaal= $row['totaal'];

    if ($totaal==$prev) {
        ++$count;
    }
    else {
        $rank = ++$count;
    }
    echo "$rank $name $totaal<br />";
    $prev = $totaal;

}

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users