At the risk of being a nuisance ( I asked a similar question yesterday but still could not get any closer to the solution).

In an sql database I have a table called 'Players' and two columns: 'Name' and 'Points'.

Suppose the values of the points are: 20000, 25000 and 30000.

Is there any way to assign a 'Rank' to those numbers with php.

So on my web page I would have something like this:

Name | Score | Rank |

Grant | 30000 | 1 |

Brett | 25000 | 2 |

Chris | 20000 | 3 |

The rank would need to be automaticallly generated depending on the scores of the players, and change accordingly when the scores change.

    Depends - Do you need to have the ranks stored in the database or is it ok if they are calculated on display.

    Easiest way to do it on display would be to: select * from players order by score desc

    Then just spit them out in the order they appear. Highest score will be first.

      I don't need to have the ranks stored in the database but I need them to display correctly when they are searched. Take a look at this link that I'm busy with and do a search. You will notice that the ranks of the search results are inaccurate (as that column actually only has an autonumber function)

      Link: http://www.cimdev.com.au/amp/frontend/leaderboard.php

        Then the select statement that I have given you should work fine. Just put it in a loop to generate a ranking based on the order.

        $query = "select * from players order by score desc";
$result = mysql_query($query);

for($i=1;$i<=mysql_num_rows($result); $i++)
{
$row = mysql_fetch_array($result);
// print out the table - use $i as rank/No.
}

          I'm not seeing it.... it looks okay to me...

          Oh... I see.... if you sort ascending you want it to go from low ranks to high ranks (in reverse)!!

          Then just run K's query, and put all that info in an array. Then sort that array however it needs to be while maintaining key associations. The key of the value is then the rank!!

            It's not just the reverse order that doesn't work. It's when the search results don't display the correct Rank.

            Kendar, your code looks cool but I'm afraid I'm still not 100% sure on how to implement it: eg how would I incorporate it into the sample code below:

            <?php require_once('../Connections/amp_connect.php'); ?>
<?php
mysql_select_db($database_amp_connect, $amp_connect);
$query_rankRS = "SELECT * FROM tbl_players ORDER BY Points DESC";
$rankRS = mysql_query($query_rankRS, $amp_connect) or die(mysql_error());
$row_rankRS = mysql_fetch_assoc($rankRS);
$totalRows_rankRS = mysql_num_rows($rankRS);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<table width="50%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>Points</td>
    <td>Rank</td>
  </tr>
  <tr>
    <td><?php echo $row_rankRS['Points']; ?></td>
    <td>????????</td>
  </tr>
</table>
</body>
</html>
<?php
mysql_free_result($rankRS);
?>

            Thanks a million.

              <?php require_once('../Connections/amp_connect.php'); ?>
<?php
mysql_select_db($database_amp_connect, $amp_connect);
$query_rankRS = "SELECT * FROM tbl_players ORDER BY Points DESC";
$rankRS = mysql_query($query_rankRS, $amp_connect) or die(mysql_error());
//$row_rankRS = mysql_fetch_assoc($rankRS);
$totalRows_rankRS = mysql_num_rows($rankRS);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<table width="50%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>Points</td>
    <td>Rank</td>
  </tr>

<?php
for($i=1;$i<=$totalRows_rankRS;$i++)
{
$row_rankRS = mysql_fetch_assoc($rankRS);
echo "
  <tr>
    <td>".$row_rankRS['Points']."</td>
    <td>".$i."</td>
  </tr>";
}
?>
</table>
</body>
</html>
<?php
mysql_free_result($rankRS);
?>

              I see the problem now though. It will work fine if you are listing all of the players but when you are selecting a subset of them with a search it wont display their actual ranking, just their ranking in the set you selected.... May have to rethink storing the rank in the database...May be the only way to get the accurate ranks on a search.

                Many thanks for all you help!

                  Write a Reply...