How to make an online leaderboard (Part 1)

A leaderboard is a must have in any actual online game, with this tutorial we will learn how to create and setup a leaderboard system, in the first two parts we talk about the server and client parts, in a engine-independent way, and the last one is focused on how to integrate it in Unreal engine 4.

Part 1: Server files
Part 2: Client files
Part 3: UE4 game integration

A leaderboard is a good way to encourage the player to replay a level taking advantage of its competitive nature.

An online leaderboard has two parts. The first part is related to the server side, we need some kind of site with online access, you can rent a web hosting or set up your own in your house. The server side is in charge of storing and updating the leaderboard data, so we need to set up a database inside it too.

If you want to test the server side in your own house before to upload to the online site you can use a WAMP server. This package has all the tools that we need to mount an online site in our machine. My favorite is Uniform Server, no installation required, just unpack and fire up.

Database

The first task is to decide what data we are going to use for the board leadership. Can be a simple number to represent the score, or a pair to order by level and score, there is a lot of options, besides this, we need to store more data like the name of the player and  an unique ID to identify the sender.

We can open the phpMyAdmin tool and create a database (TGames) and inside it two tables. The first one to store the user data (GAME1_USERS) and the second one for the leaderboard (GAME1_LEADERBOARD). Don’t make a database for each game, instead of this make a database of games and put a prefix related to the game in the name of their tables.

We are going to use the user data table only as user counter, this counter will be used during the unique ID generation. We can set the userCount as an AUTO_INCREMENT value.

In the highscores table we are going to store the user unique id generated in our php, the username to show, their highest score and a field to store the sent date (you can make a leaderboard per month or week using this value)

PHP files

The first php file is related to the connection to the database, we can split it in two files. config.php and connectdb.php, the first one to store only the connection data and the second to manage the mysql connection.

config.php

<?php
// Database config variables
define("DB_HOST", "localhost");
define("DB_USER", "your_user_name");//database username
define("DB_PASSWORD", "your_user_password");//database user password
define("DB_DATABASE", "your_database");//database name
?>

connectdb.php

<?php
class DB_Connector {
 
    // constructor
    function __construct() {}
 
    // destructor
    function __destruct() {}
 
    // Connecting to database
    public function connect() {
        require_once 'config.php';
         // connecting to mysql
        $con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);
 
        // return database handler
        return $con;
    }
 
    // Closing database connection
    public function close() {
        mysqli_close();
    }
}
?>

The third one gameFunctions.php will be used to contain the functions that can be called from the client side. In the constructor of the class we can put the constructor of our previous DB connector.

We need two functions, one to send the player score and other to retrieve the leaderboard scores.

SendScore function

If the player has userId associated we need to search for his previous record and we need to update his data. We can check if he beat his personal record at this point or/and in the client too. If the player has no userId we need to generate one before the record insertion a return this value to the client to can be stored in the player device.

public function sendScore($userId, $userName, $score, $userData)
{
	if ($userId)
	{
		//the user has an ID assigned
		$result = mysqli_query($this->connection, "SELECT GAME1_LEADERBOARD.score FROM GAME1_LEADERBOARD WHERE GAME1_LEADERBOARD.userId='$userId'");
		if ($result)
		{
			//we have a leaderboard row for the current user
			$rowDataUser = mysqli_fetch_row($result);
			$oldscore = $rowDataUser[0];
			if ($oldscore < $score)
			{
				$result2 = mysqli_query($this->connection, "UPDATE GAME1_LEADERBOARD SET GAME1_LEADERBOARD.score='$score',GAME1_LEADERBOARD.userName='$userName' WHERE GAME1_LEADERBOARD.userId='$userId'");
				if ($result2)
				{
					return array ('0', $userId); // all OK
				}
				else
				{
					return array ('1', "Error Code 1: update fail"); //error during update query
				}
			}
			else
			{
				return array ('0', $userId);// dont need update, the new score is lower than the old score
			}
		}
		else
		{
			//we need to insert a leaderboard row for the current user
			$result = mysqli_query($this->connection, "INSERT INTO GAME1_LEADERBOARD (userId,userName,score,lastDate) VALUES('$userId','$userName','$score',SYSDATE())");
			if ($result)
			{
				return array ('0', $userId); // all OK.
			}
			else
			{
				return array ('2', "Error Code 2: insert fail");//error during leaderboard insertion
			}
		}
	}
	else
	{
		// Is a new user			
		// generates a random userid , inserts score and returns the generated userid
		$result = mysqli_query($this->connection, "INSERT INTO GAME1_USERS (userName) VALUES('$userName')");
		if ($result)
		{
			$result2 = mysqli_query($this->connection, "SELECT LAST_INSERT_ID()");
			if ($result2)
			{
				$rowDataUser = mysqli_fetch_row($result2);
				$userCount=$rowDataUser[0];
				
				$userChain = '$userName'.date(DATE_RFC2822).'$userCount';
				$newuserId = md5($userChain);
				
				$result3 = mysqli_query($this->connection, "INSERT INTO GAME1_LEADERBOARD (userId,userName,score,lastDate) VALUES('$newuserId','$userName','$score',SYSDATE())");
				if ($result3)
				{
					return array ('0', $newuserId); // all OK. Returns the generated userID
				}
				else
				{
					return array ('3', "Error Code 3: insert fail");
				}
			}
			else
			{
				return array ('4', "Error Code 4: DB error"); // LAST_INSERT_ID is empty
			}
		}
		else
		{
			return array ('5', "Error Code 5: insert fail"); // new user insertion fail
		}
	}
}

GetLeaderboard function

We will return an array with the top 100 high scores, and an extra record with the current user info. During the data extraction the client must manage this structures to show only the data needed for the current screen state.

public function getLeaderboard($userId)
{
	$result = mysqli_query($this->connection, "SELECT GAME1_LEADERBOARD.userName,GAME1_LEADERBOARD.score,GAME1_LEADERBOARD.userId FROM GAME1_LEADERBOARD ORDER BY GAME1_LEADERBOARD.score DESC");
	if ($result)
	{
		$stack = array();
		$count = 1;
		
		$bestScore = 0;
		$bestPosition = 0;
		$bestName = null;
		while (($rowData = mysqli_fetch_row($result)) and ($count < 101)) // retrieves only the top 100 list
		{
			$rowUserName=$rowData[0];
			$rowScore=$rowData[1];
			array_push($stack, array($count,$rowUserName,$rowScore));
			if ($rowData[2] == $userId) // if the current userID is in the list, an extra record is returned to show the user info
			{
				$bestName = $rowData[0];
				$bestScore = $rowData[1];
				$bestPosition = $count;
			}
			
			++$count;
		}
		
		array_push($stack, array($bestPosition,$bestName,$bestScore));
		return array ('0', $stack);
	}
	else
	{
		return array ('6', "Error Code 6: retrieving list fail");
	}
}

Now we can put all together and add the caller wrapper. Our caller wrapper is a switch with the calls to our functions, we are going to use the function name as a parameter in the call from the client. It will result in a more reusable code in the future.

gameFuntions.php

<?php
$funcName = $_POST['funcName'];
 
$userId= isset($_POST['userId']) ? $_POST['userId'] : '';
$userName= isset($_POST['userName']) ? $_POST['userName'] : '';
$score= isset($_POST['score']) ? $_POST['score'] : '';
$userData= isset($_POST['userData']) ? $_POST['userData'] : '';

$functions = new gameFunctions();
// select function to execute
switch ($funcName) 
{
	case "sendScore":	list ($returnCode, $returnData) = $functions->sendScore($userId, $userName, $score, $userData); break;
	case "getLeaderboard":	list ($returnCode, $returnData) = $functions->getLeaderboard($userId); break;
	default:
		list ($returnCode, $returnData)  =  array('100', "Unknown funtion name"); break;
}

$resultado = array("returnCode"=>$returnCode, "returnData"=>$returnData);

echo json_encode($resultado);


class gameFunctions
{
	private $db;
	private $connection;
	
	// constructor
	function __construct() 
	{
		require_once 'connectdb.php';
		// connecting to database
		$this->db = new DB_Connector();
		$this->connection = $this->db->connect();
	}
	
	// destructor
	function __destruct()
	{
		$this->db->close();
	}
	
	public function sendScore($userId, $userName, $score, $userData)
	{
		// previous code go here
	}
	
	public function getLeaderboard($userId)
	{
		// previous code go here
	}	  
} 
?>

Security Annex

Before finishing with the php files we can add some security to our code to avoid the more basic cheats. We can check if the sent data is manipulated generating a hash for the data structure received and match it with the hash sent from the client for the same data structure. In this example our data structure is the result of append the data needed for the leaderboard with our secret words. We need to put the same secret words in the C++ code to match the hash. The client generates the hash for the data and sends all to the server. Our hash will be send in the userData parameter.

Finally the gameFuntions.php will look like this:

<?php
$funcName = $_POST['funcName'];
 
$userId= isset($_POST['userId']) ? $_POST['userId'] : '';
$userName= isset($_POST['userName']) ? $_POST['userName'] : '';
$score= isset($_POST['score']) ? $_POST['score'] : '';
$userData= isset($_POST['userData']) ? $_POST['userData'] : '';

$functions = new gameFunctions();
// select function to execute
switch ($funcName) 
{
	case "sendScore":	list ($returnCode, $returnData) = $functions->sendScore($userId, $userName, $score, $userData); break;
	case "getLeaderboard":	list ($returnCode, $returnData) = $functions->getLeaderboard($userId); break;
	default:
		list ($returnCode, $returnData)  =  array('100', "Unknown funtion name"); break;
}

$resultado = array("returnCode"=>$returnCode, "returnData"=>$returnData);

echo json_encode($resultado);


class gameFunctions
{
	private $db;
	
	// constructor
	function __construct() 
	{
		require_once 'connectdb.php';
		// connecting to database
		$this->db = new DB_Connector();
		$this->db->connect();
	}
	
	// destructor
	function __destruct()
	{
		$this->db->close();
	}
	
	public function sendScore($userId, $userName, $score, $userData)
	{
		if ($userId)
		{
			//the user has an ID assigned
			$tocheck = hash('sha256', $userId.$userName."secret1".$score."secret2");
			if ($tocheck != $userData)
				return array ('32', "Error Code 32: invalid user data");

			$result = mysqli_query($this->connection, "SELECT GAME1_LEADERBOARD.score FROM GAME1_LEADERBOARD WHERE GAME1_LEADERBOARD.userId='$userId'");
			if ($result)
			{
				//we have a leaderboard row for the current user
				$rowDataUser = mysqli_fetch_row($result);
				$oldscore = $rowDataUser[0];
				if ($oldscore < $score)
				{
					$result2 = mysqli_query($this->connection, "UPDATE GAME1_LEADERBOARD SET GAME1_LEADERBOARD.score='$score',GAME1_LEADERBOARD.userName='$userName' WHERE GAME1_LEADERBOARD.userId='$userId'");
					if ($result2)
					{
						return array ('0', $userId); // all OK
					}
					else
					{
						return array ('1', "Error Code 1: update fail"); //error during update query
					}
				}
				else
				{
					return array ('0', $userId);// dont need update, the new score is lower than the old score
				}
			}
			else
			{
				//we need to insert a leaderboard row for the current user
				$result = mysqli_query($this->connection, "INSERT INTO GAME1_LEADERBOARD (userId,userName,score,lastDate) VALUES('$userId','$userName','$score',SYSDATE())");
				if ($result)
				{
					return array ('0', $userId); // all OK.
				}
				else
				{
					return array ('2', "Error Code 2: insert fail");//error during leaderboard insertion
				}
			}
		}
		else
		{
			// Is a new user
			$tocheck = hash('sha256', $userName."secret1".$score."secret2");
			if ($tocheck != $userData)
				return array ('31', "Error Code 31: invalid user data");
				
			//generates a random userid , inserts score and returns the generated userid
			$result = mysqli_query($this->connection, "INSERT INTO GAME1_USERS (userName) VALUES('$userName')");
			if ($result)
			{
				$result2 = mysqli_query($this->connection, "SELECT LAST_INSERT_ID()");
				if ($result2)
				{
					$rowDataUser = mysqli_fetch_row($result2);
					$userCount=$rowDataUser[0];
					
					$userChain = '$userName'.date(DATE_RFC2822).'$userCount';
					$newuserId = md5($userChain);
					
					$result3 = mysqli_query($this->connection, "INSERT INTO GAME1_LEADERBOARD (userId,userName,score,lastDate) VALUES('$newuserId','$userName','$score',SYSDATE())");
					if ($result3)
					{
						return array ('0', $newuserId); // all OK. Returns the generated userID
					}
					else
					{
						return array ('3', "Error Code 3: insert fail");
					}
				}
				else
				{
					return array ('4', "Error Code 4: DB error"); // LAST_INSERT_ID is empty
				}
			}
			else
			{
				return array ('5', "Error Code 5: insert fail"); // new user insertion fail
			}
		}
	}
	
	public function getLeaderboard($userId)
	{
		$result = mysqli_query($this->connection, "SELECT GAME1_LEADERBOARD.userName,GAME1_LEADERBOARD.score,GAME1_LEADERBOARD.userId FROM GAME1_LEADERBOARD ORDER BY GAME1_LEADERBOARD.score DESC");
		if ($result)
		{
			$stack = array();
			$count = 1;
			
			$bestScore = 0;
			$bestPosition = 0;
			$bestName = null;
	 		while (($rowData = mysqli_fetch_row($result)) and ($count < 101)) // retrieves only the top 100 list
	 		{
	 			$rowUserName=$rowData[0];
	 			$rowScore=$rowData[1];
	 			array_push($stack, array($count,$rowUserName,$rowScore));
	 			if ($rowData[2] == $userId) // if the current userID is in the list, an extra record is returned to show the user info
	 			{
	 				$bestName = $rowData[0];
	 				$bestScore = $rowData[1];
	 				$bestPosition = $count;
	 			}
	 			
	 			++$count;
			}
			
			array_push($stack, array($bestPosition,$bestName,$bestScore));
			return array ('0', $stack);
		}
		else
		{
			return array ('6', "Error Code 6: retrieving list fail");
		}
	}	  
} 
?>

The last task is to upload the php files to our server and take note of the URL of gameFunctions.php.

Tutorial files

Now we can continue with the client side code. How to make an online leaderboard (Part 2)

2021/10/05 – Updated to PHP 7

You may also like:

How to make an online leaderboard (Part 3)How to make an online leaderboard (Part 2)

Support this blog!

For the past year I've been dedicating more of my time to the creation of tutorials, mainly about game development. If you think these posts have either helped or inspired you, please consider supporting this blog. Thank you so much for your contribution!