How to find total rows before required row

Question

I have a text based mafia game and I am selected some GameRecords. The game records are all defined in the "users" table. For this example I am using "totalcrimes". I need to select all the rows from the users table and order it by totalcrimes and then find out which row each specific user is that is viewing the page.

If I was the user that was "ranked" 30th it would echo "30". The code I use to find the top 5 is here however I need to expand on it:

<?php
$i = 0;
$FindCrimes = mysql_query("SELECT * FROM players WHERE status='Alive' AND robot = 0 ORDER BY `totalcrimes` DESC LIMIT 5");
while($Row = mysql_fetch_assoc($FindCrimes)){
    $Username = $Row['playername'];
    $TotalCrimes = number_format($Row['totalcrimes']);
    $i++;
        echo "
        <tr>
            <td bgcolor='#111111' width='5%'>$i</td>
            <td bgcolor='#111111' width='50%'><a href='viewplayer?playerid=$Username'>$Username</a></td>
            <td bgcolor='#333333' width='45%'>$TotalCrimes</a></td></td>
        </tr>
        ";
    } 
?>

Answer 1

I am going to assume that you already have a variable set to hold the current users ID number and total crimes, so in this case I will use $user as my variable.

Change yours to fit.

Now, I see 2 instances in which you could mean as your post wasn't very specific, so I will address both.

To show the number at the top of the page, you would use something like;

<?php
$sql   = "SELECT * FROM `players` WHERE `totalcrimes` > '{$user['totalcrimes']}'";
$run   = mysql_query($sql);
$rank  = mysql_num_rows($run) + 1;
echo 'Your rank: ' . $rank;

Other than that, I see it's possibly being used to highlight your row, so something like this would suffice;

<?php
$i = 0;
$FindCrimes = mysql_query("SELECT * FROM players WHERE status='Alive' AND robot = 0 ORDER BY `totalcrimes` DESC LIMIT 5");
while($Row  = mysql_fetch_assoc($FindCrimes)) 
{
    $Username    = $Row['playername'];
    $TotalCrimes = number_format($Row['totalcrimes']);
    $i++;

    $primary   = '#111111';
    $secondary = '#333333';

    if ($Row['id'] == $user['id']) 
    {
        $primary   = '#222222';
        $secondary = '#444444';
    }

    echo "<tr>
        <td bgcolor='$primary' width='5%'>$i</td>
        <td bgcolor='$primary' width='50%'><a href='viewplayer?playerid=$Username'>$Username</a></td>
        <td bgcolor='$secondary' width='45%'>$TotalCrimes</a></td></td>
    </tr>";
} 

If neither of those give your requirements, please comment and I'll edit to suit.

edit: I've worked on games for a few years - care to share the link to yours?

Answer 2

This can do the trick

SELECT COUNT(*)+1 as rank 
FROM users
WHERE totalcrimes > (SELECT totalcrimes 
                     FROM users 
                     WHERE user_id='12345' AND status='Alive' AND robot='0');

So it counts all rows with greater totalcrimes than selected user (in this example I have used user_id column and some id 12345), than adds 1 on that sum and returns as rank value.

Course, modify WHERE clause inside the brackets to make it work for you.

I assumed that table name is users and user's id is integer user_id.

Test preview (Navicat Premium):

What this query does? It returns number of selected rows + 1 as rank column, from the table users where totalcrimes is greater than totalcrimes of some user. That user's totalcrimes is selected by another query (by its user_id). If you have multiple users with same totalcrimes value, this query will return same rank for all of them.