Some days ago I was working in a quiz project. Where user will play quiz and for each correct answer they will earn points. One of the task of this quiz application was, to get rank of a particular user. Now I am showing how could I solve this problem using mysql query.
Here is the table structure:
CREATE TABLE IF NOT EXISTS `quiz_user` ( `uid` BIGINT UNSIGNED NOT NULL , `participated` SMALLINT UNSIGNED NULL DEFAULT 0 , `correct` SMALLINT UNSIGNED NULL DEFAULT 0 , `wrong` SMALLINT UNSIGNED NULL DEFAULT 0 , `created` DATETIME NULL , `updated` DATETIME NULL , PRIMARY KEY (`uid`) ) ENGINE = InnoDB
This table contains user records of quiz game. Here is an examples:
| uid | participated | correct | wrong | created | updated |
| 6508097 | 3 | 6 | 1 | 2010-02-26 06:51:01 | 2010-02-26 06:51:01 |
| 14535811 | 2 | 4 | 2 | 2010-02-26 06:51:01 | 2010-02-26 06:51:01 |
For simplicity here is only 2 users. But It may contain 1 to unlimited users. So to get rank each time you’ve to check what the status of the user, how many correct answers each user solved. For better performance you can do caching. Now I’m showing how a user will get rank from this table.
Long time ago, I saw a solution of a programmer. Look how he did the solution
function getUserRank($userId){
// no limit they did the query to get all result
$sql = "SELECT * FROM quiz_user ORDER BY correct DESC";
$result = mysql_query($sql);
$rows = '';
$data = array();
if (!empty($result))
$rows = mysql_num_rows($result);
else
$rows = '';
if (!empty($rows)){
while ($rows = mysql_fetch_assoc($result)){
$data[] = $rows;
}
}
// now they did a php loop to get the user rank by user id
$rank = 1;
foreach($data as $item){
if ($item['uid'] == $userId){
return $rank;
}
++$rank;
}
return 1;
}
Just look at the as usual or brute force solution. This is simply a fucky code. To check each user’s rank you first retrieve all records from mysql query then using a loop you check what the user’s rank. So if the table contains 10000 users and suppose 1000 users concurrently playing this game, then just think how much time will take this function and thus this will decrease performance of your application.
So the question is how could you write the optimal solution for this problem. If you make rank using mysql query then it would be more optimal and faster for your application. Here I’m showing how could I solve the problem:
The MySQL query that solved my problem is here:
SET @rownum := 0;
SELECT rank, correct FROM (
SELECT @rownum := @rownum + 1 AS rank, correct, uid
FROM quiz_user ORDER BY correct DESC
) as result WHERE uid=xxxxxxxx
Look carefully the solution. If you run this query directly in mysql either using phpmyadmin or other mysql admin tools, then you’ll get the user’s ‘xxxxxxxx’ rank in quiz. Let’s explain the query:
SET @rownum := 0;
In mysql you can assign variable and can use later. So at first I declare a variable rownum and asing 0 as value. To declare a variable in mysql you have to use ‘@’ sign before variable name.
Then I used a subquery within query. Look the subquery. This is the query that actually makes the rank and returns result as a table. This is a simple query where I’ve just added “@rownum := @rownum + 1 AS rank” so you assigned rank in each row.
SELECT @rownum := @rownum + 1 AS rank, correct, uid FROM quiz_user ORDER BY correct DESC
So after this query’s result now I check what the user’s rank
SELECT rank, correct FROM result WHERE uid=xxxxxxxx;
where result is
SELECT @rownum := @rownum + 1 AS rank, correct, uid FROM quiz_user ORDER BY correct DESC;
So the complete query is:
SET @rownum := 0;
SELECT rank, correct FROM (
SELECT @rownum := @rownum + 1 AS rank, correct, uid
FROM quiz_user ORDER BY correct DESC
) as result WHERE uid=xxxxxxxx
Now look the php solution:
function getUserRank($userId){
$sql1 = "SET @rownum := 0";
$sql2 = "SELECT rank, correct FROM (
SELECT @rownum := @rownum + 1 AS rank, correct, uid
FROM quiz_user ORDER BY correct DESC
) as result WHERE uid=$uid";
// here model.php is a class for database connectivity
include_once "model.php";
global $dbconfig; //configuration of database that I assigned in config file
$md = new Model($dbconfig);
$md->connectDb();
mysql_query($sql1); /*as mysql_query function can execute one query at a time */
$result = mysql_query($sql2);
$rows = '';
$data = array();
if (!empty($result))
$rows = mysql_num_rows($result);
else
$rows = '';
if (!empty($rows)){
while ($rows = mysql_fetch_assoc($result)){
$data[] = $rows;
}
}
$md->closeDb();
//rank of the user
if (empty($data[0]['rank']))
return 1;
return $data[0]['rank'];
}
As you make the ranking in mysql, so this is much faster solution than the ordinary solution. By this way I solved the problem and get the user’s rank in my quiz. You can use this technique for any game or application. For more better performance you can use caching. Hope this article will help you for better understand.
Thanks for article. Very good application of variables usage in MySQL queries.
Excellent! Thank you for sharing this. Looking forward to more.
Thanks for sharing. Just a question: how would you sort out if two or more user has the same value for correct answers?They should not have different ranks.
@alle, this is the actual code where if both user have same correct answer, then they have the same rank. But for simplicity I didn’t mention that earlier. Here I’ve used IF logic in mysql
Thanks for sharing this Code You Are the best
Please help I tried your code but the result is all rank no. 1 below is my code.
$sqlGetRank = @mysql_query("SELECT STUDENTNUM,GWAGRADE FROM gwa"); while($rs = mysql_fetch_array($sqlGetRank)) { $stdID = $rs['STUDENTNUM']; $rank = getUserRank($stdID); echo $stdID ." " .$rs['GWAGRADE']." " .$rank .''; } function getUserRank($userId){ $sql1 = "SET @rank = 0, @prev_val = NULL;"; $sql2 = "SELECT rank,GWAGRADE FROM ( SELECT @rank := IF(@prev_val=GWAGRADE,@rank,@rank+1) AS rank, @prev_val := GWAGRADE AS GWAGRADE,STUDENTNUM FROM gwa ORDER BY GWAGRADE DESC ) as result WHERE STUDENTNUM=$userID"; mysql_query($sql1); /*as mysql_query function can execute one query at a time */ $result = mysql_query($sql2); $rows = ''; $data = array(); if (!empty($result)) $rows = mysql_num_rows($result); else $rows = ''; if (!empty($rows)){ while ($rows = mysql_fetch_assoc($result)){ $data[] = $rows; } } //rank of the user if (empty($data[0]['rank'])) return 1; return $data[0]['rank']; }It appears that this query is doing a table scan, is that the case?
It would be helpful if you can explain what you mean by “rank” and why this query is difficult for mysql to execute efficiently.
Its really great
It solves my problem
Thanks
I propose the next solution as a better solution…
It is a pure sql solution… What do you think about it?
At first I get (with a sub select sql) the groups of users that have better or equal correct answers than the user I asking for. This is the code for that:
If you have which are the groups with more or equal correct answers you will have which is its rank.
Here you have te complete SQL statment:
<?php
if(isset($_POST['s1']))
{
$u1=$_POST['e1'];
$u2=$_POST['e2'];
include ('db.php');
$sql1 = "SET @rank := 0";
mysql_query($sql1);
$sql2="SELECT @rank:=@rank+1 AS ran,price,aid
FROM myfavorite
ORDER BY price DESC";
$result1=mysql_query($sql2);
//$result=mysql_query("select
//* from myfavorite ORDER BY price DESC");
echo "
AID
MOBILE
PRICE
RANK
“;
while($row = mysql_fetch_array($result1))
{
echo “”;
echo “” . $row['aid'] . “”;
echo “” . $row['mobile'] . “”;
echo “” . $row['price'] . “”;
echo “” . $row['ran'] . “”;
echo “”;
}
echo “”;
}
//$row3=mysql_query(“select position,securemark
//rank() ORDER BY securemark
//from student1″);
//while($row1 = mysql_fetch_array($row3))
//{
// $a1= $row1['position'] ;
//}
?>
Sorry but there was a little equal sign in the last statement sent… Here is the final SQL:
@Fidel Clotet
Dude, don’t confuse people by writing that complex sub queries. I have tried your solution thinking it a better solution and it took 1hr for me to figure it out. Where as the solution given by “mahmud ahsan” is simply awesome and it took 5mins to get it right.
Very good post Ahsan.. Keep it up.
@Dharam
Well, the Fidel’s query was in fact the one that showed me how to solve my problem. So don’t criticize who only wants, and in fact did, help with his own point of view.
Thanks Fidel and Ahsan.
@Dharam
I understand your observation y conclusion.
But think about this: the time it takes you to understand depends on what commonly you do…
Commonly you use procedural programming. The SQL is a declarative language.
Take in count that, i.e., using SQL programming gives more opportunities to the DB engine for optimization.
Here you have a little simpler option (without having):
select count(*) as rankForMyUid from (select correct, count(*) from quiz_user group by correct ) as groupsByCorrect where correct>=(select correct from quiz_user where uid=$uid);Another solution
SELECT count(DISTINCT correct) FROM quiz_user WHERE correct > (select correct from quiz_user where uid=$uid)
SELECT count(DISTINCT correct) FROM quiz_user WHERE correct > (select max(correct) from quiz_user where uid=$uid)
thank u, sub query may return only one rows ! max(correct)
Hi
I’m writing a user ranking module for a site. This ranking depends on some criterias and it’s possible to set or unset any one of these criterias in order to consider them in calculating the user rank or not. And here’s the way I’ve implemented the ranking calculation :
when I set one or more of the criterias to be considered in ranking , for each user in the system I insert one record for each criteria , for example :
if I have 2 criterias and both are set and consider that I have two users , I’ll have :
Ranking table
————–
username | criteria | to_be_added | score
————————————————–
user1 | criteria1 | 1 | 0
user1 | criteria2 | 1 | 0
user2 | criteria1 | 1 | 0
user2 | criteria2 | 1 | 0
It means I just set the to_be_added field to 1 for all of them and leave the calculation of score for each criteria for each user to the time the user logins so that to prevent doing all these calculations at once , because there are a huge number of users …
But there is one problem , if I want to show for example the best user (based on the highest score) , the result can’t always be true because some users might not logged in at that time and their score might be zero .
As far as you’ve also written a ranking script I’ve decided to get help from you , how do you think I should solve this problem ?
Thanks in advance for your help …
Try to think simple way. Hope you’ll solve it by yourself. Be confident.
Thanks for the sharing, really a great information. It solved my problem too. Thanks for sharing mahmud. Another great solution & sharing, appreciate that.
Thank you for sharing this with the community.
This is indeed a much better solution than computing rating server-side !
Cheers keep up the good work
How would you group by uid? Assuming you insert a new row if the user does the same quiz, it’ll throw off the ranking
Thanks a lot Mr. mahmud ahsan , your code is working fine for me….
Is this example using mySQL ver 5.1?
I have a similar ranking query that has worked fine in ver 5.0, but doesn’t sort correctly in ver 5.1. It seems to calculate “@rownum := @rownum + 1″ before it applies the ordering.
This is very helpful website..
This is very helpful and informative blog….
Thanks for sharing information..
nice one i really like it..
Your topics are best, Thanks for this post here.
I have a problem that :
Can i use MySQL database in my local pc to connect with the Facebook application ?
I have used but error has come.
my connection code is :
$hostname = “localhost”;
$database = “bdnamel”;
$user = “username”;
$pass = “”;
$db = mysql_connect($hostname, $user,$pass);
mysql_select_db($database,$db);
Please suggest me….
The error is the following:
PHP Warning: mysql_connect() [function.mysql-connect]: Access denied for user ‘root’@'localhost’ (using password: NO) in
PHP Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in
PHP Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in
PHP Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in
What’s the problem for which above error ?
If you want to use your pc’s web server for facebook app, you need to have a dedicated IP that points to your pc. Mysql is not related with it, because it will control from the web server, so far if you’ve dedicated IP you can use both. By the way your error says, invalid password and username so please check it.
Thanks for reply.
Actually I have solved my problem before some moments of your reply.
I have a Quiz Application in facebook platform and when I click the submit button after correct the question then form data would not submit in the result page Internet Explorer but in Firefox this is correct.
Also if I try secondly after first time in IE the result of a form data would submit and result is come.
I was spent my huge time to correct this but i can’t do it.
What is the problem and which will be suggesion to me to solve this problem.
Please suggest me…
Solution:
SELECT count( DISTINCT username )
FROM ranking_table
WHERE score > (
SELECT max( score )
FROM ranking_table
WHERE `username` = “tony”
GROUP BY `username` )
Blogs are so interactive where we get lots of informative on any topics nice job keep it up !!
I am trying to get the ranking of a users friend from the table.. need to extract only the users who are friends of the current user but the rank must be their actual rank in the entire ranking system (I call it world)
WHERE u.user_id IN (“.$friendIds.”)
GROUP BY u.user_id
ORDER BY account_balance DESC
LIMIT 0,25
and
WHERE u.user_id IN (“.$friendIds.”)
GROUP BY u.user_id
ORDER BY earning_sum DESC
LIMIT 0,25
giving the friendIds limits the extraction to only friends but it messed the rank also as u see I have a limit of 25, if I continue with this limit and remove friendIds clause i.e. hit world ranking and try to extract friends from php then friends having rank over 25 are not shown.over
any help in this regard would be highly appreciated.
Regards
Warning: Missing argument 1 for getUserRank(), called in /pool.php on line 180 and defined in /pool.php on line 154
1
i am having error i just make table like u and use this code
code don’t show in my last post here is code
Hey, thank you so much for this! It’s very helpful! I’m only having one issue, that I’m sure is a simple one. Here’s the code I’m using-
$rank = mysql_query(“SET @rownum := 0;
SELECT rank, power FROM (
SELECT @rownum := @rownum + 1 AS rank, power, id
FROM players ORDER BY power DESC
) as result WHERE id=’$id’”);
And the code works great when I put it in PHPMyAdmin, the only trouble I’m having is printing out the result in PHP on my webpage. I tried echoing $rank and doing $rankfind = mysql_fetch_array($rank); echo $rankfind['rank'];
but that didn’t work either. Quick fix anyone?
Thanks a lot Mahmud.
A variation for calculating the percentile along with rank –
set @totalStudents = 0;
select count(*) into @totalStudents from marksheet;
SELECT id, score, @curRank := IF(@prevVal=score, @curRank, @studentNumber) AS rank,
@percentile := IF(@prevVal=score, @percentile, (@totalStudents – @studentNumber + 1)/(@totalStudents)*100),
@studentNumber := @studentNumber + 1 as studentNumber,
@prevVal:=x
FROM marksheet, (
SELECT @curRank :=0, @prevVal:=null, @studentNumber:=1, @percentile:=100
) r
ORDER BY score DESC
Great writeup… Thanks for providing the code and walking through it!
i have table
create table polls(
id int not null auto_increment primary key,
title varchar(400) NOT NULL,
description varchar(500) NOT NULL,
activation_time DATE NOT NULL,
expire_time DATE NOT NULL,
is_published boolean NOT NULL Default false);
when i update
update rating set is_published = true where id = ?
that time is_published field updeted bat activation_time also update and gate the system time and chang the activation_time time
i want only update is_published field what can i do plz help me my database is mysql 5.5
i have table
create table polls(
id int not null auto_increment primary key,
title varchar(400) NOT NULL,
description varchar(500) NOT NULL,
activation_time DATE NOT NULL,
expire_time DATE NOT NULL,
is_published boolean NOT NULL Default false);
when i update
update polls set is_published = true where id = ?
that time is_published field updeted bat activation_time also update and gate the system time and chang the activation_time time
i want only update is_published field what can i do plz help me my database is mysql 5.5
Saved my day buddy!
Now a days facebook web applications developing are highly demand-able.
What is the best way to retrieve some of the IDs from the INSERT statement without using an SQL query statement again?
Here is the closest one that I could see, but can’t get it to work:
$sqlQuery = “INSERT INTO orders (item,count) VALUES (‘beef’, ’1′) RETURNING receiptNumber INTO @invoiceNum”;
The error is straightforward, but don’t know how to solve it.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘RETURNING receiptNumber INTO @invoiceNum’ at line 1
Any pointers would be appreciated. FYI: This is done through Php / MySQL.
Thanks Mr. Ahsan…I am become fan of you..Really great.
You are the man! I have looked at several ways to implement this and by far this has been the easiest solution yet! I am not an SQL or PHP Guy but this worked for me very quickly…
Woot! Woot!
Yet another solution, standard ANSI SQL with no sub-selects:
SELECT COALESCE(COUNT(qu2.uid), 0) + 1 as rank, MAX(qu.correct) as correct
FROM quiz_user qu
LEFT JOIN quiz_user qu2 ON qu.correct < qu2.correct
WHERE qu.uid = xxxxxxxx
Using mysql to substitute 25 lines of php code, similar to your example.
Many people don’t know about the power of mysql, probably due to its not-so-easy syntax.
But as soon as you know, it becomes a powerful tool (esp. regarding performance)!
Have any of you guys bothered to see the explain plan of your queries? It is amazing how to get rank every one of the solutions here is doing a full table scan. Also, it is equally amazing to see your PHP code loading full tables without any predicate. That just tells me that none of you have seen tables with million of rows!
There is no sane way to rank rows in MySQL. You are doing an expensive operation that may work for small tables but will never work for huge tables. Consider working with a small set of data using keys.
Yes sir, we didn’t work on big table, so would you please write a post how to work on big table for this problem, so we can learn about that.
I want to sum(numbers) instead correct colum, because I have a marksheet and I want to sum numbers in this query instead correct colum with I have some users at same rank. Please help.
I too am working on a large dataset (about 3 million). We’re trying to get a rank for a specific UserID, along with 9 results above it.
This in SQL, without “filesort” or a full table scan, is incredibly tough. If anyone has managed to come up with a solution, be my guest to post it here!
@schodemeiss I did a test with 500k rows with a 40% improvement by limiting the inner query to an approximate limit and adjust the offset
You could, for example, store last computed rank for a user and use it +/- 10000 position to limit your query.
Example with last_rank at 100000:
SET @rownum := 0;
SELECT rank, correct FROM (
SELECT @rownum := @rownum + 90001 AS rank, correct, uid
FROM quiz_user ORDER BY correct DESC LIMIT 90000,20000
) as result WHERE uid=xxxxxxxx
Saved my day buddy!
You came to my rescue.
I’m not that experienced with SQL and thought of the brute force approach first for assigning same rank to players having the same score. Modified your query to fit my game and it works like a charm. 
Cheers!!
Helped me in assigning ranks for on my game site.Thanks
I want to sum(numbers) instead correct colum, because I have a marksheet and I want to sum numbers in this query instead correct colum with I have some users at same rank. Please help.
Helped me a lot in developing my next facebook-app.
Tnx!
There is quite a bit of spam here
I NEED HELP WITH MYSQL RANK.
PLEASE WHENEVER THE SUBJECT SCORE IS ABOVE 99 (e.g 100), THE RANKING GETS IT WRONG. IF IT WERE TO WORK PROPERLY, THE RANKING OF 100 SCORE SHOULD BE 1ST , BUT WITH THE BELOW SQL IT RETURNS LAST POSITION.
NOTE: ALL SCORES BETWEEN 1-99 WORKED PERFECTLY (PERFECT RANKING).
KINDLY HELP OUT: MY EMAIL ADDRESS IS DELUXE_K@YAHOO.COM