Generating a Leaderboard with Ranking for MySQL

Follow Me on Pinterest

Generating a Leaderboard with MySQLRecently I have had to build a leaderboard that uses one MySQL table to store a list of users and another MySQL table to store a list of competition entries and their scores for each week of a 6 week quiz.

The challenge was to generate a leaderboard that was flexible enough to allow me to select the top 100 overall quiz players, get a player by their rank number, determine at what rank the player is currently placed and also to allow me to drill down to the results of a specific week thus creating a series of leaderboards for each week.

I was able to do this by writing a series of derived queries to first generate the table for an overall list of users and their scores. In order to determine a rank a MySQL variable was used to hold the rank number so it can be assigned to each user.

First the database schema.

//The users table
CREATE TABLE `tbl_players` (
  `player_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email_address` varchar(500) NOT NULL,
  `date_registered` datetime NOT NULL,
  PRIMARY KEY (`player_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

//The entries table
CREATE TABLE `tbl_entries` (
  `entry_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `status` int(3) NOT NULL,
  `player_id` bigint(20) NOT NULL,
  `week_no` bigint(20) NOT NULL,
  `score` int(11) NOT NULL,
  `correct_answers` smallint(6) NOT NULL,
  `date_entered` datetime NOT NULL,
  PRIMARY KEY (`entry_id`),
  UNIQUE KEY `player_id` (`player_id`,`week_no`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

The entry table has a unique key covering the players id and the week number that they are entering. Now we have the table schema the next step for me was to generate a mysql query to lift out the top 100 players from the table who have scores registered for any week they have played. I also only wanted to retrieve people that have registered a score.

/* Generate our leaderboard for our overall top 100 players for all weeks */

//build query
$sql = "SELECT rank_number, player_id, name, score ";
$sql .= "FROM ( ";
$sql .= "	SELECT @rank:=@rank+1 AS rank_number, player_id, name, score ";
$sql .= "	FROM ( ";
$sql .= "		SELECT pl.status, pl.player_id, pl.name, SUM(en.score) AS score ";
$sql .= "		FROM tbl_players pl ";
$sql .= "		JOIN tbl_entries en ON pl.player_id = en.player_id";
$sql .= "		WHERE score != 0 ";
$sql .= "		GROUP BY pl.player_id ";
$sql .= "		ORDER BY score DESC";
$sql .= "	) AS rankings, (SELECT @rank:=0) AS r ";
$sql .= ") AS overall_rankings ";
$sql .= "LIMIT 0, 100; ";

// Perform Query
$result = mysql_query($sql);

//user result and show players
while ($row = mysql_fetch_assoc($result)) {
    echo $row['rank_number'];
    echo $row['player_id'];
    echo $row['name'];
    echo $row['score'];
}

//free the result
mysql_free_result($result);

This successfully returns us the top 100 players in our leaderboard. But this is for all 6 weeks overall. So what if we want to only get scores based on users that have entered just for a certain week.

/* Generate our leaderboard for our top 100 players for a certain week running for the quiz */

//assign the week number
$weekno = 3;

//build query
$sql = "SELECT rank_number, player_id, name, score ";
$sql .= "FROM ( ";
$sql .= "	SELECT @rank:=@rank+1 AS rank_number, player_id, name, score ";
$sql .= "	FROM ( ";
$sql .= "		SELECT pl.status, pl.player_id, pl.name, SUM(en.score) AS score ";
$sql .= "		FROM tbl_players pl ";
$sql .= "		JOIN tbl_entries en ON pl.player_id = en.player_id";
$sql .= "		WHERE score != 0 ";
//here we only want score pulled out for that particular weeks quiz
if ($weekno > 0){
	$sql .= "AND en.week_no = " . $weekno . " ";
}
$sql .= "		GROUP BY pl.player_id ";
$sql .= "		ORDER BY score DESC";
$sql .= "	) AS rankings, (SELECT @rank:=0) AS r ";
$sql .= ") AS overall_rankings ";
$sql .= "LIMIT 0, 100; ";

// Perform Query
$result = mysql_query($sql);

//user result and show players
while ($row = mysql_fetch_assoc($result)) {
    echo $row['rank_number'];
    echo $row['player_id'];
    echo $row['name'];
    echo $row['score'];
}

//free the result
mysql_free_result($result);

There may also be a time that we may want to get a specific user at a certain rank. Lets say we want to get the top 3 users to display them on a podium. We would achieve this by just adding a clause to our derived tables.

/* Generate our leaderboard for our top 100 players for a certain week running for the quiz */

//assign the week number
$weekno = 0;

//build query
$sql = "SELECT rank_number, player_id, name, score ";
$sql .= "FROM ( ";
$sql .= "	SELECT @rank:=@rank+1 AS rank_number, player_id, name, score ";
$sql .= "	FROM ( ";
$sql .= "		SELECT pl.status, pl.player_id, pl.name, SUM(en.score) AS score ";
$sql .= "		FROM tbl_players pl ";
$sql .= "		JOIN tbl_entries en ON pl.player_id = en.player_id";
$sql .= "		WHERE score != 0 ";
//here we only want score pulled out for that particular weeks quiz
if ($weekno > 0){
	$sql .= "AND en.week_no = " . $weekno . " ";
}
$sql .= "		GROUP BY pl.player_id ";
$sql .= "		ORDER BY score DESC";
$sql .= "	) AS rankings, (SELECT @rank:=0) AS r ";
$sql .= ") AS overall_rankings ";
//get the top three users from our overall leaderboard
$sql .= "WHERE rank_number >= 1 ";
$sql .= "AND rank_number <= 3";

// Perform Query
$result = mysql_query($sql);

//user result and show players
while ($row = mysql_fetch_assoc($result)) {
    echo $row['rank_number'];
    echo $row['player_id'];
    echo $row['name'];
    echo $row['score'];
}

//free the result
mysql_free_result($result);

This will now give us our top 3 positions in the leaderboard so we can find out our Gold, Silver and Bronze medla winners!

The next step was to build something that will allow us to tell a user at what place they are in the leaderboard. For example we may want to tell someone that they are “Ranked in nth position in our leaderboard”. This is now made easy through our derived table query.

/* Get a particular players leaderboard rank */

//function to add an ordinal value to the number
function showOrdinal($number)
{
	// first convert to string if needed
	$num = (string) $number;
	// now we grab the last digit of the number
	$last_digit = substr($number, -1, 1);
	// if the string is more than 2 chars long, we get
	// the second to last character to evaluate
	if (strlen($number)>1)
	{
		$next_to_last = substr($num, -2, 1);
	}
	else
	{
		$next_to_last = "";
	}
	// now iterate through possibilities in a switch
	switch($last_digit)
	{
		case "1":
			// testing the second from last digit here
			switch($next_to_last)
			{
				case "1":
					$number.="th";
					break;
				default:
					$number.="st";
			}
			break;
		case "2":
			// testing the second from last digit here
			switch($next_to_last)
			{
				case "1":
					$number.="th";
					break;
				default:
					$number.="nd";
			}
			break;
		// if last digit is a 3
		case "3":
			// testing the second from last digit here
			switch($next_to_last)
			{
				case "1":
					$number.="th";
					break;
				default:
					$number.="rd";
			}
			break;
		// for all the other numbers we use "th"
		default:
			$number.="th";
			break;
	} 

	// finally, return our string with it's new suffix
	return $number;
}

//assign the week number
$weekno = 2;

//build query
$sql = "SELECT rank_number, player_id, name, score ";
$sql .= "FROM ( ";
$sql .= "	SELECT @rank:=@rank+1 AS rank_number, player_id, name, score ";
$sql .= "	FROM ( ";
$sql .= "		SELECT pl.status, pl.player_id, pl.name, SUM(en.score) AS score ";
$sql .= "		FROM tbl_players pl ";
$sql .= "		JOIN tbl_entries en ON pl.player_id = en.player_id";
$sql .= "		WHERE score != 0 ";
//here we only want score pulled out for that particular weeks quiz
if ($weekno > 0){
	$sql .= "AND en.week_no = " . $weekno . " ";
}
$sql .= "		GROUP BY pl.player_id ";
$sql .= "		ORDER BY score DESC";
$sql .= "	) AS rankings, (SELECT @rank:=0) AS r ";
$sql .= ") AS overall_rankings ";
//get the top three users from our overall leaderboard
$sql .= "WHERE player_id = 56 ";
$sql .= "LIMIT 0, 1";

// Perform Query
$result = mysql_query($sql);

//user result and show players
while ($row = mysql_fetch_assoc($result)) {
    echo $row['name'] . " is currently ranked " . showOrdinal($row['rank_number']);
}

//free the result
mysql_free_result($result);

This will now give us the players position in week 2. If you want to know their overall position then just change the week number to 0.

That’s it! We now have a MySQL query that can handle different scenarios.

There is one final thing that we have not taken into account. What if a user is ranked 4th for example and the user below has the same score but is ranked 5th. We may want to give them the same rank number. This can be achieved by simply storing the previous score for the user that we are ranking. From there we can determine if the score is the same and act accordingly.

/* Generate our leaderboard for the top 100 players whilst giving users with the same score the same rank */

//assign the week number
$weekno = 0;

//build query
$sql = "SELECT rank_number, player_id, name, score ";
$sql .= "FROM ( ";
$sql .= "	SELECT @rank:=IF(@last_score=score, @rank, @rank+1) AS rank_number, player_id, name, score, @last_score:=score AS previous ";
$sql .= "	FROM ( ";
$sql .= "		SELECT pl.status, pl.player_id, pl.name, SUM(en.score) AS score ";
$sql .= "		FROM tbl_players pl ";
$sql .= "		JOIN tbl_entries en ON pl.player_id = en.player_id";
$sql .= "		WHERE score != 0 ";
//here we only want score pulled out for that particular weeks quiz
if ($weekno > 0){
	$sql .= "AND en.week_no = " . $weekno . " ";
}
$sql .= "		GROUP BY pl.player_id ";
$sql .= "		ORDER BY score DESC";
$sql .= "	) AS rankings, (SELECT @rank:=0) AS r, (SELECT @last_score:=0) AS l ";
$sql .= ") AS overall_rankings ";

// Perform Query
$result = mysql_query($sql);

//user result and show players
while ($row = mysql_fetch_assoc($result)) {
    echo $row['rank_number'];
    echo $row['player_id'];
    echo $row['name'];
    echo $row['score'];
}

//free the result
mysql_free_result($result);

This will now show the leaderboard but anyone who is found to have the same score will be ranked the same!

Hope you find this useful.

Filed Under: Blog, Code Share
Tags: , , , .
Bookmark: permalink.

Let me know what you think:

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>