PHP: A "Rank" System

Question

I have a mysql database that consists of users. In that database, there is a "score" column. I want to make a profile for the users, but it will show their rank. I don't want to do this server-sided, so i'm trying to do it from the profile page itself. This is my current code:

$sql3 = mysql_query("SELECT * FROM members ORDER BY score DESC") or die("Could not allocate information!");
$rank = 0;
while(mysql_fetch_assoc($sql3)){
  $rank++;
}
echo "<b>Rank: </b>#$rank<br/>";

Ive also tried:

$sql3 = mysql_query("SELECT * FROM members ORDER BY score DESC") or die("Could not allocate information!");
$rank = 0;
while(mysql_fetch_assoc($sql3)){
  $rank++;
  echo "<b>Rank: </b>#$rank<br/>";
}

Either way out, every user has the same rank, #2. Is there any way for me to do this? And if you need more information, please just comment, don't down-rep me. Thanks.

Try `echo`ing inside the `while` loop, just after setting `$rank`. In your second try, you used `$num` instead of `$rank`. — Aiias, Mar 30 '13 at 20:28
Welcome to Stack Overflow! [Please, don't use `mysql_*` functions](http://stackoverflow.com/q/12859942/1190388) in new code. They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the red box? Learn about prepared statements instead, and use [tag:PDO] or [tag:MySQLi]. — hjpotter92, Mar 30 '13 at 20:30
Sorry guys, I fixed my code. It use to be num, but its now rank. Either way out, it is still showing everybody as rank 2, and if I add the echo in the while, they get ranks 1-2(I have 2 users). — Robert Doe, Mar 30 '13 at 20:31
Don't you expect them to get ranks 1 and 2 when echoing in the while? — Aiias, Mar 30 '13 at 20:33
Have you considered using the `mysql_num_rows()` function, instead of fetching results in a loop without using them? — Sverri M. Olsen, Mar 30 '13 at 20:34
Not exactly. I want the user with the most score(50000) to be "Rank: #1" and the user with the next most(5000) to be "Rank: #2" and so on. — Robert Doe, Mar 30 '13 at 20:35
@SverriM.Olsen I have not, but can you show me an example so that I can test out the use and tell whether it works or not. — Robert Doe, Mar 30 '13 at 20:36
Would changing `mysql_fetch_assoc` to `mysql_fetch_array` make a difference? — y--, Mar 30 '13 at 20:48

score 0 · Answer 1 · answered Mar 30 '13 at 20:30

0

Have you tried:

$sql3 = mysql_query("SELECT * FROM members ORDER BY score DESC") or die("Could not allocate information!");
$rank = 0;
while(mysql_fetch_assoc($sql3)){
    $rank++;
    echo "<b>Rank: </b>#$rank<br/>";
}

answered Mar 30 '13 at 20:30

Joe F

642
4
12

Yes. This gives me: "Rank: #1 Rank: #2" – Robert Doe Mar 30 '13 at 20:32

score 0 · Answer 2 · answered Mar 30 '13 at 20:34

0

Just use the <ol> [ordered list] tag:

<ol>
<?php while($user = mysql_fetch_assoc($sql3)): ?>

    <li><?php echo $user['name']; ?></li>

<?php endwhile; ?>
</ol>

Guessing you want to display some property of the user [e.g. name or what you call it in your table], otherwise it does not makes much sense to display a list of numbers.

Anyway mysql_* functions are deprecated, please move to PDO or mysqli.

answered Mar 30 '13 at 20:34

moonwave99

21,957
3
43
64

What im trying to do is find where the user stands out of all others on the site and then display their "ranking". – Robert Doe Mar 30 '13 at 20:39
This is what the code does [according to you original MySQL query]. – moonwave99 Mar 31 '13 at 02:36

y-- · Answer 3 · 2013-03-30T20:56:08.117

0

Try something like this:

$sql3 = mysql_query("SELECT * FROM members ORDER BY score DESC") or die("Could not allocate information!");
$rank = 0;
while($row = mysql_fetch_assoc($sql3)) {
     $rank++;
     if ($row['user_id'] == $current_pages_user_id){
           echo "<b>Rank: </b>#$rank<br/>";
     }
}

edited Mar 30 '13 at 20:56

answered Mar 30 '13 at 20:35

y--

588
2
10
27

I dont want to display the users score. I want to take their score and figure out where the player stands throughout all of the other users – Robert Doe Mar 30 '13 at 20:42
But I want anybody to see the persons rank... not just the profile owner – Robert Doe Mar 30 '13 at 20:58
@RobertDoe Yes this would still work. the `$current_pages_user_id` variable would be set to the profile's owner's id, if that makes sense. – y-- Mar 30 '13 at 20:59
1

Do you have the ability to get the current profile's owner's id correctly? – y-- Mar 30 '13 at 21:06
@RobertDoe How does your system work? Does the user have it's own folder? Or does it use GET? If it uses GET to well, get the data, then you can use it to get the user's id number. It then checks if that id number is equal to the one produced by `$sql3` – y-- Mar 30 '13 at 21:23

user1823799 · Answer 4 · 2013-03-30T20:45:36.217

0

Try

while($user = mysql_fetch_assoc($sql3)) {    
  $rank++;    
  echo "<b>#$rank. $user['name'] Score: </b> $user['score']<br />";    
}

But as hjpotter92 pointed out, look into using mysqli or pdo.

edited Mar 30 '13 at 20:45

answered Mar 30 '13 at 20:35

user1823799

120
7

this displays ranks 1-2(I have 2 users). I only want to display 1 users rank which would be found on how much score they have – Robert Doe Mar 30 '13 at 20:41
That is what im looking for. Now I want it to where it only shows the information for a specific username. – Robert Doe Mar 30 '13 at 20:49
Aha, add to your sql query: WHERE userId = X or similar. Like: 'SELECT * FROM members WHERE userId = 5 ORDER BY score DESC' but then you can skip the loop and instead do: `$sql = mysql_query('SELECT * FROM members WHERE userId = 5 ORDER BY score DESC LIMIT 1'); $user = mysql_fetch_assoc($sql); echo "#$rank. $user['name'] Score: $user['score']
";` – user1823799 Mar 30 '13 at 20:51
What about the rank at that point? You cant query it and it cant count itself(not trying to sound like a jerk). So what would I do with the "$rank"? – Robert Doe Mar 30 '13 at 20:56
Yes, you're right. Keep the loop and skip the where and limit part. Instead you can add an if statement in your loop to check if you have found the right user, something like `if($user['id'] == 5) echo "#$rank...` – user1823799 Mar 30 '13 at 21:00
Also check my other answer for an other kind of solution. – user1823799 Mar 30 '13 at 21:16

score 0 · Answer 5 · answered Mar 30 '13 at 21:11

I think what you really are trying to do is something like this.

$result = mysql_query("SELECT member_name, score,
       (SELECT COUNT(*)
        FROM members ORDER BY score DESC) AS rank
FROM members
WHERE member_id = 5
LIMIT 1") or die("Could not allocate information!");

$member = mysql_fetch_assoc($result);

echo "<b>#$member['rank']. $member['name']</b> Score:  $member['score']<br />";

As others mentioned before, check out the mysqli and pdo.

PHP: A "Rank" System

5 Answers5