CODEWITHSUNDEEP
mysqlsqldatabase
drpelz
drpelz

Reputation: 811

How to get ranking of specific player?

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

Upvotes: 1

Views: 98

Answers (3)

Orel Eraki
Orel Eraki

Reputation: 12196

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.

Upvotes: 1

federicot
federicot

Reputation: 12341

$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'"
);

Upvotes: 2

krokodilko
krokodilko

Reputation: 36107

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."'

Upvotes: 1

Related Questions