Echo the variable of i in while

Question

i try something like this :

function userrank($userid){
    $sql = mysql_query("SELECT * FROM users ORDER BY atacs DESC");
    $i   = 1;
    while ($row = mysql_fetch_assoc($sql)) {
        if ($row['username'] == $userid) {
            echo 'You are on ' . $i . ' in the general leaderbord';
        }
    }
}

On the leaderboard it shows me the rank correctly, but i want to show me on another page too , on the "youraccount" page , for this i try to make this function. what is wrong ?

`$i` never changes. you probably want to slap a `$i = $i + 1` in there somewhere... — Marc B, Jan 02 '15 at 19:45
Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in new code. They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). — Jay Blanchard, Jan 02 '15 at 20:05

Len_D · Answer 1 · 2015-01-02T20:36:11.453

0

You have to increment $i. I'm Assuming that the leader board order is represented by the result of your query. So, if true, change your code as follows:

function userrank($userid){
    $sql = mysql_query("SELECT * FROM users ORDER BY atacs DESC");
    $i   =0; // starts out with an unknown rank.
    while ($row = mysql_fetch_assoc($sql)) {
     $i++; // increment $i here.
        if ($row['username'] == $userid) {
            echo 'You are on ' . $i . ' in the general leaderbord';

        }
    }
}

The $i will increment for sure. So, if it is not working still, I'd look to see what the result of your query is. Try echoing $row['username'] and see what the value is and then compare that to the echoed calue of $userid.

edited Jan 02 '15 at 20:36

answered Jan 02 '15 at 19:55

Len_D

1,422
1
12
21

If it is represented by that order Then increment should be just before if statement.... Because he passing a single user id.What is the point in incrementing $i after echoing... – Indra Kumar S Jan 02 '15 at 20:02
I was thinking that the IF statement should cause the increment. Otherwise, that is not the user being represented, so why increment the counter? But I see your point. I will edit the answer. Thanks. – Len_D Jan 02 '15 at 20:05

score 0 · Accepted Answer · answered Jan 02 '15 at 20:44

Basically what you're doing here is counting how many users have an atacs greater than or equal to $userid's atacs. Problems with this:

Terribly inefficient. Note the database retrieves and sends to your while loop an entry for every user, even those who have an atacs less than the $userid. All but one of these while loop iterations does nothing by design. Lots of wasted time sending data from the database to PHP, which doesn't even use it.
Pulls way more data back than is necessary. You end up with every row for every user in your entire users table - but your result is just a scalar number ( how many users with > score ).
Actually gives you wrong results in the case that your score is tied with others'. In this case some users with the same score may be counted as "above" the user, others as "below the users".

Databases are good at iterating over data; it's all "locally" accessible and the database engine can make many optimizations if you can describe in SQL what you are trying to accomplish. So instead of doing it that way, why not just do everything in the database?

set @user_atacs = ( select atacs from users where id = 12 );
select count(*) +1 from users where atacs > @user_atacs;

I've mocked up the table here: http://sqlfiddle.com/#!2/ff9a86/3

This solution essentially just counts the number of users with a higher atacs than the current user. All users with the same score will get the same rank, and the next rank will be appropriately higher, so it doesn't suffer from any of your method's errors.

As a final note, the most appropriate way to do something like leaderboards is probably to precompute the leaderboard periodically and then use the results to show each user's position in the leaderboards, rather than trying to compute it on the fly for each user. But that's even farther out of scope :)

Echo the variable of i in while

2 Answers2