Ranking system based on user points...

[vishalraj9]

Hi all,
I was trying to rate the members according to the points they received & store it against their ID in the same table. For example:


User Points
A 10
B 30
C 20
D 50
E 40
(what code goes in here to make it like: )



User Points Rank
A 10 5
B 30 3
C 20 4
D 50 1
E 40 2
And update the database with those results?

Would appreciate it if anyone can guide me with this ! Thank you in advance.

[simplistik]

Realistically you wouldn't update the "Rank" in the database, you'd process it on the frontend w/ server-side code, one way (as there are others):

semi-pseudo code


// mysql connection crap
$query = mysql_query("SELECT * FROM table ORDER BY points DESC");
$i = 0;
while ( $row = mysql_fetch_array($query) )
{
$i++;
echo "rank: $i / user: $user / points: $points <br/>";
}

if you really wanted to store it in the db, each time the points are updated you'd just run an update script, that would reorder the rank information each time, using a similar counter method as above.

[vishalraj9]

Thanks for your response, simplistik . I wanted to store that in the table. I was thinking of writing it in a script & run it as a cron every 5 hours or so. As I was waiting for the replies, I was thinking over it & I even thought, I
might get a situation where it might look like:


User Points Rank
A 10 4
B 30 2 ------- Same points ~ Same rank ???
C 20 3
D 50 1
E 30 2 ------- Same points ~ Same rank ???


As you can see, we have taken only 5 members & tried to allocate them the ranks which is including duplicate ranks. If there are at least 1000 members, realistically, at one time there might be 10 or even more members sharing the same number of points that means I would encounter duplicate ranking for all of them & I would need to stop that from happening. I think I would have to take any other factor apart from points, into consideration to be able to do this. So how do I then change it & store the ranks in the DB ? In fact, I am lost at the point where I can assign the rank to the member. How do I do that?

Please let me know. Thank you.

[Esherido]

I really think you should not store this information in the database. From the component separation perspective, you would want to have the user and their points in the database since it is very static compared to ranking. You would then store their ranking in a more agile form, such as in the cache (memcache, file-system, etc.), so that it can be more quickly modified and used repeatedly by your application with minimal work.

If you insist on storing ranks in the database, you would want to add a column in the database with the rank. Then you fetch your entire list of users and sort them by their score. Then you iterate over the list of users (Sorted) and assign the rank, adding one to the rank as you go through unless the user before the current user has the same points.

$rank = 0;
for($i = 0; $i < count($sorted_users); $i++){
if($previous["points"] == $sorted_users[i]["points"]){
$sorted_users[i]["points"] = $rank;
}else{
$rank++;
$sorted_users[i]["points"] = $rank;
}
$previous = $sorted_users[$i];
}Best luck!

[Jeff Wheeler]

Depending on the type of database you’re using, and your database experience, I think it might also be possible to define a function (or stored procedure? I don’t think that’s right) which you can select as an additional column just like the others. This might facilitate getting the ranking of a specific user without having to select all the users, but the underlying function would still need to analyze the relative value of points (and therefore still get a count of all users with more points).

Another solution entails listening for changes to a point value cell from within MySQL, and setting a trigger to increment/decrement a rank cell for all necessary rows. This is probably a much more efficient solution than the ones proposed above, because you only need to select the specific user to find a rank, rather than all users above (or below) him. In other words, it caches the value like you want, without the need to have your PHP script slow down waiting for the process to finish.

[Esherido]

^ Like Jeff said, if you're using PostgreSQL, you could likely use a stored procedure to speed things up exponentially. As far as I know, if you're using MySQL, you'll have to do some heavy lifting on the PHP side.

[Charleh]

I don't see why you can't update the table from a trigger perspective - how often does the data in the table get modified?

If we are talking once every few minutes or a few times a minute (or less) then a trigger is fine, it's transactional and can update the rank information factoring an insert, update or deletion.

I also propose that the user that got the score first gets the higher rank - two records wont share the same timestamp unless they were inserted in one transaction, so therefore inserting the timestamp and using this as part of the rank calculation would work.

Using a trigger means that the data would only get updated when the data changes (causing the most minimum of processing). The only issue with a trigger is that they can be slow if not optimised (read: badly written)

You can't really calculate rank on the PHP server without selecting the entire user table to sort the data by (big no no), and doing it via a dynamic SQL statement or a static statement is slower than using a sproc/trigger as it is not pre-compiled and the execution plan must be calculated on the fly. (though repeated visits would make use of the cache)

Oh yes, MySQL does stored procedures by the way, in case anyone didn't know :P



you would want to have the user and their points in the database since it is very static compared to ranking


I also disagree with this - seeing as rank is directly based on user points, rank is just as dynamic as user points and therefore needs to be updated each and every time user points are updated or users are added/removed.

Alternatively you don't need to store rank, you can just select the data out based on the rank ordering (i.e. order by userpoints, dateadded) and you will automatically have the rank order - albeit with no pre defined row numbering (you can do that in PHP though)

[vishalraj9]

Hi all,
I will try to follow your solutions & let you know how that works.

Thank you.

[vishalraj9]

Hi Esherido,
I was trying to deal with your code, but I am unable to understand how the $sorted_users came into picture & what's the criteria for obtaining that. Can you please let me know. I have been trying to do it in various ways, but no success. Maybe I am missing a key element here???

Please let me know. Thank you.

[srinathmkce]

Hi friend,

I too struck with the same prob.. but after some hours i got the output. I ve shared my coding below..

Student database .. table name mid 1.. fields are rollno, tot, rank

sample data

rollno tot rank

1 100 1

2 200 2

3 200 2

4 300 4

Note: Since i am using rank for my students i have to skip numbers if they got repeated. In the above example rollno 2 and 3 got same rank as 2 . Hence i skipped the rank 3 and moved to 4.




<?php
$i=1;
$j=0;
$con=mysql_connect("localhost","root","");
if(!$con)
{
die("Couldn't connect to the database");
}
mysql_select_db("student",$con);
$sql=mysql_query("select * from mid1 order by tot DESC") ;
while($row=mysql_fetch_assoc($sql))
{
$id=$row['rollno'];
$total=$row['tot'];
$sql3="select * from mid1 where tot='$total'";
$rowcount=mysql_num_rows(mysql_query($sql3));
if($rowcount==1)
{
$sql2=mysql_query("update mid1 set rank='$i' where rollno='$id'");
mysql_query($sql2,$con);
$i=$i+1;
}
if($rowcount>1)
{
$j=$j+1;
if($j==$rowcount)
{
$sql2=mysql_query("update mid1 set rank='$i' where rollno='$id'");
mysql_query($sql2,$con);
$i=$i+$rowcount;
$j=0;
}
else
{
$sql2=mysql_query("update mid1 set rank='$i' where rollno='$id'");
mysql_query($sql2,$con);
}
}

}


?>










If u dont want to skip the ranks simply delete the variable $j and the line $i=$i+$rowcount.

I am novice in php.. pls notify if i ve done any mistakes.

Thank u.