CODEWITHSUNDEEP
phpmysqlsql
Mansa
Mansa

Reputation: 2325

Get user's position in a leaderboard?

I'm making this game leaderboard and I am trying to get a specific user's position in this leaderboard. I have tried this but with no luck:

SELECT SUM(points) as points 
FROM publicpoints 
GROUP BY fk_player_id 
WHERE points > (SELECT SUM(points) as points 
                FROM publicpoints 
                WHERE fk_player_id = $playerid)

How should I go about this?

---------------- EDIT -----------------

I have also tried like this, but still no luck but maybe this claryfies what I try to accomplish:

$sql = mysql_query("SELECT SUM(points) mypoints FROM ".$prefix."_publicpoints WHERE fk_player_id=$playerid");
$row = mysql_fetch_assoc($sql);

$mypoints = $row['mypoints'];

$sql = mysql_query("SELECT SUM(points) as points FROM ".$prefix."_publicpoints 
                        WHERE points < $mypoints
                    GROUP BY fk_player_id");
$row = mysql_fetch_assoc($sql);

$rank = mysql_num_rows($sql);

if(!empty($mypoints)){
    $rank = $row['rank']+1;
} else {
    $rank = 0;
}

Upvotes: 1

Views: 1386

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

WHERE clause must be before the GROUP BY clause like so:

SELECT 
  SUM(points) as points 
FROM publicpoints 
WHERE fk_player_id = $playerid
GROUP BY fk_player_id;

Then you can use the ORDER BY with LIMIT to get a specific Points position:

SELECT 
  SUM(points) as points 
FROM publicpoints 
WHERE fk_player_id = $playerid
GROUP BY fk_player_id
ORDER BY points
LIMIT 1 -- for top 1;

Update: To get how many users who has higher points than the current user, you can do this:

SELECT COUNT(*) AS NumberOfPlayers
FROM
(
    SELECT 
       fk_player_id
    FROM points 
    GROUP BY fk_player_id
    HAVING SUM(points) > (SELECT SUM(points)
                          FROM points 
                          WHERE fk_player_id = 5)
) t;

If you want to get those players that has higher points than the current user, you can do this:

SELECT 
   fk_player_id
FROM points 
GROUP BY fk_player_id
HAVING SUM(points) > (SELECT SUM(points)
                      FROM points 
                      WHERE fk_player_id = 5);

Updated SQL Fiddle Demo

Upvotes: 4

Related Questions