Adding a rank using php and mysql

Question

I have some php code for pulling results from my database:

<?php  $res=mysql_query("SELECT SUM(step_count.steps) as total, 
leagues.league_id, leagues.league_name
    FROM step_count
    INNER JOIN logins on
      step_count.email=logins.email
    INNER JOIN leagues ON
      leagues.email=logins.email
    GROUP BY leagues.league_id, leagues.league_name
    ORDER BY `total` DESC LIMIT 10");

    while ($row = mysql_fetch_array($res))
    {
      ?>

      <?php echo $row['league_name']; ?>
      <?php echo $row['total']; ?>

And I get the results as wanted which are

Test      | 6200 
TestTwo   | 5600 
TestThree | 3400

And I was wondering if there was a way to create rankings for these results so it looks like this without having to manually add the ranks myself

#1 Test      | 6200 
#2 TestTwo   | 5600 
#3 TestThree | 3400

It's already sorting by the total, I would just like it to adding a ranking score next to it — Phoebe, Oct 10 '17 at 09:55
on what parameter do you want to rank? if it's an existing you can add an ORDER statement, or if you want some sort of display order you could add an display order column... — Odyssey1111, Oct 10 '17 at 09:56
I don't have a table for the ranking score, I just simply want them to be listed as #1 if they have the highest score and so forth — Phoebe, Oct 10 '17 at 09:58
Don't use the deprecated and insecure mysql*-functions. They have been deprecated since PHP 5.5 (in 2013) and were completely removed in PHP 7 (in 2015). Use MySQLi or PDO instead. 2. You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use [Prepared Statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead of concatenating your queries, which can be used if you use the above mentioned MySQLi or PDO. — Milan Chheda, Oct 10 '17 at 09:59

score 0 · Accepted Answer · answered Oct 10 '17 at 09:58

As per your question you want to show number of column try this code

<?php  
    $res=mysql_query("SELECT SUM(step_count.steps) as total, 
    leagues.league_id, leagues.league_name
        FROM step_count
        INNER JOIN logins on
          step_count.email=logins.email
        INNER JOIN leagues ON
          leagues.email=logins.email
        GROUP BY leagues.league_id, leagues.league_name
        ORDER BY `total` DESC LIMIT 10 ");
        $rank = 1;
        while ($row = mysql_fetch_array($res))
        {
    ?>
    <?php echo '#'.$rank++; ?>
    <?php echo $row['league_name']; ?>
    <?php echo $row['total']; ?>
<?php } ?>

Adding a rank using php and mysql

1 Answers1