Skip to main content
A
Anonymous
February 14, 2012
Answered

Updating scores table and updating rank with php.

  • February 14, 2012
  • 1 reply
  • 2649 views

Hi

I have a scores table on my site that is 

Scores : id - userid - rank - points -

While using the site  users get points for doing stuff. The points are updated, but I don`t know how

to update the rank of that user by comparing every one elses points and updating the rank numbers

based on the users with most points in php.

Hope you can help. Thanks

This topic has been closed for replies.
Correct answer bregent

Weird, the link I provided is not what I intended; it looks much different and is difficult to read. Anyway, you don't mention what is going wrong with the code you provided. I also don't think you want to prefix the alias rank with the table name 'scores' as it's not a table column. I don't use MySQL, but I think you want to use '@' for your local SQL variables, so something like:

$sqlgo = mysql_query("SET @rank=0;SELECT @rank:=@rank+1 AS rank, Users.id, Users.username, Users.profilepic, Scores.scoreid, Scores.userid, Scores.level, Scores.points, rank, Scores.badges FROM Scores INNER JOIN Users ON Scores.userid=Users.id ORDER BY points DESC LIMIT 30");

1 reply

B
bregent
Participating Frequently
February 14, 2012

If the rank is based on the scores, then you would not want to store that in a database table as that's a violation of normalization. You would simply need to calculate it.

http://www.fromdual.com/ranking-mysql-results

A
Anonymous
February 15, 2012

Hi Bregent

Thanks so much for the reply. Read the post and I have been trying to adopt it to my code:

It looks some thing like this:

$rank=0;

$sqlgo = mysql_query("SELECT $rank:=$rank+1 AS rank, Users.id, Users.username, Users.profilepic, Scores.scoreid, Scores.userid, Scores.level, Scores.points, Scores.rank, Scores.badges FROM Scores INNER JOIN Users ON Scores.userid=Users.id ORDER BY points DESC LIMIT 30");

$realtime="<table width='719' border='0' align='center'>";

 

while($row=mysql_fetch_assoc($sqlgo))

{

$scoreid=$row['scoreid'];

$userid=$row['userid'];

$username=$row['username'];

$userphoto=$row['profilepic'];

$level=$row['level'];

$points=$row['points'];

$rank=$row['rank'];

$badges=$row['badges'];

//Profile photo sorting.

$profilephoto="<img src='$userphoto' width='50' height='50'>";

$realtime.="<tr>

    <td width='130' height='101' align='center'><font size='4'>rank</font></td>

    <td width='149'  align='center'>$username<br/>$profilephoto</td>

    <td width='152'  align='center'><font size='4'>$points</font></td>

    <td width='144'  align='center'><font size='4'>$level</font></td>

    <td width='138'  align='center'><font size='4'>$badges</font></td>

    </tr>";

}

$realtime.="</TABLE>";

echo "<div align='center'>$realtime</div>";

The <td> with rank is supposed to show the numbers of ranking like 1 to users with the least amount of points.

Hope you can help. My efforts to modify exsisting code has been pretty hard because of the table joining etc.

Thanks.

B
bregentCorrect answer
Participating Frequently
February 15, 2012

Weird, the link I provided is not what I intended; it looks much different and is difficult to read. Anyway, you don't mention what is going wrong with the code you provided. I also don't think you want to prefix the alias rank with the table name 'scores' as it's not a table column. I don't use MySQL, but I think you want to use '@' for your local SQL variables, so something like:

$sqlgo = mysql_query("SET @rank=0;SELECT @rank:=@rank+1 AS rank, Users.id, Users.username, Users.profilepic, Scores.scoreid, Scores.userid, Scores.level, Scores.points, rank, Scores.badges FROM Scores INNER JOIN Users ON Scores.userid=Users.id ORDER BY points DESC LIMIT 30");

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices