How to get ranking of specific player?

Question

I have a list of players. The players are sorted by points. What I'd like to know is how do I get the ranking number of a CERTAIN player?

This is my code so far (which doesn't work because it seems to have some bugs):

$rank = mysql_query (SET @rank := 0;
SELECT *, @rank := @rank + 1
FROM ava_users
WHERE id = '".$id."'
ORDER BY points DESC);

$rank_res = mysql_fetch_array($rank);

When I try to use my query I get an error message:

mysql_fetch_array() expects parameter 1 to be resource, boolean given in     /Users/***/Documents/Arcades/Arc_development/arc_projects/***/arc_dev_website/arc_offline/includes/profile/profile_main.inc.php

possible duplicate of [mysql\_fetch\_array() expects parameter 1 to be resource, boolean given in select](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in-select) — putvande, Aug 18 '13 at 19:46

score 2 · Accepted Answer · answered Aug 18 '13 at 19:51

2

$rank = mysql_query (
    "SELECT a.*, 
            (
            SELECT  COUNT(1)
            FROM    ava_users b
            WHERE   (b.points, b.id) >= (a.points, a.id)
            ) AS rank
    FROM    ava_users a
    WHERE   a.`user` = '$id'"
);

answered Aug 18 '13 at 19:51

federico-t

12,014
19
67
111

Orel Eraki · Answer 2 · 2013-08-19T12:20:25.847

1

user is a key word, therefore use user in order to check parameters equality. Also, mysql_query can only execute 1 query at a time.

edited Aug 19 '13 at 12:20

answered Aug 18 '13 at 19:44

Orel Eraki

11,940
3
28
36

score 1 · Answer 3 · answered Aug 18 '13 at 19:48

1

Try this:

SELECT `user`, rank
FROM (
  SELECT `user`, ( @rank := @rank + 1 ) as rank
  FROM ava_users, ( select (@rank := 0 ) ) rnk
  ORDER BY points DESC
) ranks
WHERE `user` = '".$id."'

answered Aug 18 '13 at 19:48

krokodilko

35,300
7
55
79

Hm. I always get the same error message (see above in my post when I try to use it). – drpelz Aug 18 '13 at 19:55
The error messages are gone (this works, too)! Thank you very much for your help!:D – drpelz Aug 18 '13 at 20:04

How to get ranking of specific player?

3 Answers3