CODEWITHSUNDEEP
phpmysqlsql
Khalid Okiely
Khalid Okiely

Reputation: 111

SQL query construction

I recently created a scoring system where the users are ordered by their points on descending basis. First I used to store ranks in a column of its own. I used to run this loop to update the rank:

$i = 1;
    $numberOfRows = mysql_query('SELECT COUNT(`id`) FROM sector0_players');
    $scoreboardquery = mysql_query("SELECT * FROM sector0_players ORDER BY points DESC");
    while(($row = mysql_fetch_assoc($scoreboardquery)) || $i<=$numberOfRows){
        $scoreid = $row['id'];
        $mysql_qeury = mysql_query("UPDATE sector0_players SET scoreboard_rank = '$i' WHERE id = '$scoreid'");
    $i++;
    }

And it was really hard, not to mention slow to actually run this on a huge amount of users. Instead, I tried to construct a query and ended up with this.

SET @rownum := 0;
SELECT scoreboard_rank, id, points
FROM (
SELECT @rownum := @rownum + 1 AS scoreboard_rank, id, points FROM sector0_players ORDER BY points DESC
)
as result WHERE id = '1';

But, this is just a select statement. Is there anyway I could get around it and change it so that it updates the table just as the loop does?

Upvotes: 0

Views: 237

Answers (2)

Prabhuram
Prabhuram

Reputation: 1268

Please try using the following query :

set @rownum:=0;
update sector0_players set scoreboard_rank=@rownum:=@rownum+1 ORDER BY points DESC;

PHP code can be ,

mysql_query("set @rownum:=0;");
mysql_query("update sector0_players set scoreboard_rank=@rownum:=@rownum+1 ORDER BY points DESC;");

Upvotes: 1

JRK
JRK

Reputation: 182

You can try using the RANK function .. I haven't actually executed the SQL, but it should work

UPDATE sector0_players
SET scoreboard_rank =
(
SELECT srank
FROM
(
    SELECT id,points, RANK() OVER (ORDER BY points) AS srank
    FROM sector0_players T
) D
WHERE D.id = sector0_players.id
AND  D.points = sector0_players.points
)

Upvotes: 0

Related Questions