Advertisement

How to Code a Self-Hosted PHP/SQL Leaderboard For Your Game

by
Student iconAre you a student? Get a yearly Tuts+ subscription for $45 →

In this article, we're going to create our first MySQL leaderboard to host on a website or web server using simple PHP and some SQL. We'll then make a simple Unity example in C# using GUIText objects to add new scores to our leaderboard, display the top ten scores and display a user's score and rank.


Introduction

Single player games are fun, but beating your own highscore can get dull. Adding a leaderboard to your game provides real motivation for players to improve their scores and play your game more, and it can even be used to figure out if your game is too easy or hard. In games that continue forever, leaderboards can be the sole reason your players play. If you have your own website or server, you might want to host your own leaderboard, so you have complete control over your game.


Creating Your Leaderboard

First of all, you're going to need to have an SQL database on your server or site. Websites often come with a built-in MySQL database. Details of this will vary depending on what service you use, but you should be able to find your SQL host, username and password (as well as your database name) from your admin panel or registration email.

In this example, phpMyAdmin is used to access the database (built right into the admin panel). You'll want to open up your database and open the SQL tab. If you have more control over your server, you can create a new database.

Next, insert the following SQL:

CREATE TABLE Scores(
    name VARCHAR(10) NOT NULL DEFAULT 'Anonymous' PRIMARY KEY,
    score INT(5) UNSIGNED NOT NULL DEFAULT '0',
    ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)
ENGINE=InnoDB;
Self_Hosted_PHP_SQL_Leaderboard_table

This will create a table with three variables:

  • name, which holds your users' names, and which will store 10 characters. This is our table's main identifier, so that means that it can only store one row per username.
  • score, which holds each user's highest score. In this example it's an unsigned variable, so it can only be positive. If you want to have negative scores, you'll have to change that.
  • ts, a timestamp we can use to change the order of our leaderboard.

Now, if you're using SQL Server and not MySQL, you can still use TIMESTAMP, but for the value you'll have to use GETDATE() instead of CURRENT_TIMESTAMP.

One extra thing to keep in mind: If you're making a very simple game, you might not want to tie scores to names (in order to allow each player to have several scores in your Top 10, for instance). This can be a bad idea though; you might have one player who's so good they can dominate your whole Top 10! In this case you won't want name as a primary key, and you'll want to add this too:

id INT(8) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

This will make sure a discernible new row is added for each score.

Click Go and you're done! Your table's all ready now.


Setting Up Your PHP Files

Now you need to create some PHP files. These are the middle men of the operation, providing a way for Unity to access your server. The first PHP file you're going to need is AddScore.php. You'll need to know the server information from before.

<?php
        $db = mysql_connect('SQLHOST', 'SQLUSER', 'SQLPASSWORD') or die('Failed to connect: ' . mysql_error());
        mysql_select_db('YOURDATABASE') or die('Failed to access database');

(Replace SQLHOST, SQLUSER, SQLPASSWORD and YOURDATABASE with your own information.)

Here we've just tried to connect to the database. If the connection fails, Unity will be informed that the request was unsuccessful. Now, you're going to want to pass some information to the server:

$username = mysql_real_escape_string($_GET['name'], $db);
$score = mysql_real_escape_string($_GET['score'], $db);
$hash = $_GET['hash'];
$privateKey="ADDYOURKEY";

The hash is used to encrypt your data and stop people from hacking your leaderboard. It gets generated with a hidden key in Unity and here, and if the two hashes match you're allowed access to your database.

$expected_hash = md5($username . $score . $privateKey);
if($expected_hash == $hash) {

Here we generate the hash ourselves and check that the hash we submit from Unity is identical to the hash we expect. If it is, we can send our query!

$query = "INSERT INTO Scores
SET name = '$name'
   , score = '$score'
   , ts = CURRENT_TIMESTAMP

This is the first half of our SQL query. It should be fairly self-explanatory; the submitted score and username are added to the table, and the timestamp is updated. The second half is more complicated:

ON DUPLICATE KEY UPDATE
   ts = if('$score'>score,CURRENT_TIMESTAMP,ts), score = if ('$score'>score, '$score', score);";

We first check whether the username (our primary key) already has a row. If it does, instead of inserting a new entry, our entry will be updated. We want to update the timestamp and score, but only if the new score is higher!

Using if statements, we make sure that the new values are only used if the new score is greater than the current score, otherwise the original values are used.

        $result = mysql_query($query) or die('Query failed: ' . mysql_error());
        }
?>

Finally we run our query and close our PHP.

This file goes on our server. You'll need to remember the URL. Likewise, we need to make two more PHP files with different queries, which we'll call TopScores.php and GetRank.php.

The TopScores query is simply:

SELECT * FROM Scores ORDER by score DESC, ts ASC LIMIT 10

This will take the top 10 values based on score, and for tied ranks puts players who obtained the score first furthest up the table. This time we want to extract data too, so we also add:

$result_length = mysql_num_rows($result);

for($i = 0; $i < $result_length; $i++)
{
     $row = mysql_fetch_array($result);
     echo $row['name'] . "\t" . $row['score'] . "\n";
}

This will extract our results and tabulate them in a way we can put them into arrays in Unity.

Finally, we have GrabRank:

SELECT  uo.*,
    (
    SELECT  COUNT(*)
    FROM    Scores ui
    WHERE   (ui.score, -ui.ts) >= (uo.score, -uo.ts)
    ) AS rank
FROM    Scores uo
WHERE   name = '$name';

This will give us our player's rank in the scoreboard. We can then extract it by echoing $row['rank'].

Our source code also includes a sanitiser function, which will prevent users from inputting swear words to your leaderboard, or attempting a SQL injection attack.


Making a Simple Minigame in Unity

Now we need a game to use our highscore board with! We're just going to test how many clicks each user can do in ten seconds, but you can add your leaderboard to any game.

Layout

We'll start by making four GUIText objects. These should be anchored about the middle centre for convenience. You can adjust these with pixel offset to get them in the right place, but if you want them to adjust their position for any resolution, it's simpler to change the X and Y position (between 0 and 1); otherwise, you'll have to adjust them at startup.

Self_Hosted_PHP_SQL_Leaderboard_layout

You will, however, have to adjust the font size at startup if you want to run at all resolutions. A quick way to do this is by basing them on the screen's height. We can do this by making a class that does this and attaching it to all our text objects, but it's much simpler to do this all from one class.

It doesn't really matter what object we choose as our "manager", so we can just put this class on our click counter. So in our first class we write:

void Start(){
    foreach(GUIText chosentext in FindObjectsOfType(typeof(GUIText)) as GUIText[])
    {
        chosentext.blah.fontSize = Mathf.FloorToInt(Screen.height * 0.08f);
    }
}

This will find every text object in the scene and scale it to a sensible size.

Now we want the click counter to be larger than the other text, so if we stick this class on there we have the added bonus that we can also check if the guiText in question is the one attached to this GameObject:

if (blah == guiText) blah.fontSize = Mathf.FloorToInt(Screen.height * 0.18f);
else [etc.]

Gameplay

The clicking component of the game will be very simple. At the start, we don't want the timer to count down until the first click, so we'll make two private bools in our class - firstClick and allowedToClick. In Start() we can set firstClick to false and allowedToClick to true.

Now we need the counter to actually record the clicks, and there are a couple of ways of doing this. We could keep an integer variable that tracks the score, or we could make it slightly less efficient but in one line (and with something so simple we don't really need to optimise, but it's good practice). So we'll register the click in the Update() function, and increment the value by reading the string.

void Update () {
    if (allowedToClick && Input.GetMouseButtonUp(0))
    {
        if (!firstClick)
        {
            firstClick = true;
            StartCoroutine(Countdown());
        }
        guiText.text = (System.Int32.Parse(guiText.text) + 1).ToString();
    }
}

As you can see here, the incrementation is achieved by reading the string as an integer, adding one, and then converting back to a string. You'll also see here that we've run a coroutine as soon as the user first clicks, which starts the countdown.

We'll use recursion in this function. Again, we could use an integer that holds the countdown value for efficiency, but we'll use string manipulation again.

IEnumerator Countdown()
{
    yield return new WaitForSeconds(1);
    counter.guiText.text = (System.Int32.Parse(counter.guiText.text) - 1).ToString();
    if (counter.guiText.text != "0")
    {
        StartCoroutine(Countdown());
    }
    else
    {
        allowedToClick = false;
        GetComponent<HighScore>().Setscore(System.Int32.Parse(guiText.text));
        toptext.guiText.text = "Enter your username.";
        GetComponent<NameEnter>().enabled = true;
    }
}

Note: it's important that we used StartCoroutine() and didn't just call this function, because it's an IEnumerator. The yield statement causes it to wait for a second before any action is taken. It removes one from the counter, and if the value is not zero, it calls itself again. This way the function will count down until it reaches 0.

Name Entry

After this, it stops the user from clicking, asks for the username, and accesses our second and third classes (which we're about to write!). We'll have a look at what these do now, starting with NameEnter.

In NameEnter() we're going to allow a user to type in their username, with some constraints. Initially we want to display the underscore character _, which will be erased as soon as they start typing their name. On top of this, we don't want them to be able to use characters like \ or , as these would mess up our SQL queries.

We're going to use a string builder to create this. First we'll place some variables at the top of our class:

private int MaxNameLength=10;
private StringBuilder playerName;
private bool backspacepossible;
private bool initialpress;

The MaxNameLength should be set to the same length as you used for your VARCHAR length when you made your table. Here we have our string builder, playerName, and two Booleans. The first, backspacepossible, is to control the user's ability to hold down backspace to erase characters. The second is to indicate whether they've started typing their name yet.

In Start(), we need to take care of a few things. We'll disable all text except for the one called Toptext; we can do that in a foreach loop, like before.

void Start()
{
    foreach(GUIText text in FindObjectsOfType(typeof(GUIText)) as GUIText[]){
        if (text.name != "Toptext")
        {
            text.guiText.enabled = false;
        }
    }

    GetComponent<ClickTimes>().enabled = false;
    playerNameTemp = new StringBuilder();
    playerNameTemp.Append("_");
    backspacepossible = true;
    initialpress = false;
}

Here you can see we've done a few things. We've disabled our initial class (ClickTimes) as we aren't using it any more. We've also created an instance of playerNameTemp and appended it with _, so players can see where their name goes, and we've initialised our variables.

Now we need to allow the player to actually enter their name. At the end of Update() we place the following snippet:

guiText.text = playerNameTemp.ToString()

This will make sure the text displays what our string builder records.

Next we handle character input:

if (playerNameTemp.Length < MaxNameLength)
{
    foreach (char c in Input.inputString)
    {
        if (char.IsLetterOrDigit(c) || c == '_' || c == ' ')
        {
            if (!initialpress)
            {
                initialpress = true;
                playerNameTemp.Remove(0, 1);
            }
            playerNameTemp.Append(c);
        }
    }
}

So, provided the string builder's length is less than the maximum name length, and as long as the user is inputting characters which are either letters, digits, spaces or underscores (although you might choose only to allow alphanumeric characters), the string will be appended with the new digit. In the case that this is the first press, the original underscore will be removed before the new letter is added.

Next:

if(playerNameTemp.Length>0){
if (Input.GetKeyDown(KeyCode.Backspace)){
    if (!initialpress)
    {
        initialpress = true;
    }
    backspacepossible = false;
    StartCoroutine(BackspaceInitialHold());
    playerNameTemp.Remove(playerNameTemp.Length - 1, 1);
}
else if(backspacepossible&&Input.GetKey(KeyCode.Backspace)){
    backspacepossible = false;
    StartCoroutine(BackspaceConstantHold());
    playerNameTemp.Remove(playerNameTemp.Length - 1, 1);

As long as there are no characters remaining in our string builder and backspace is possible, the user can remove characters. Note the difference between the first and second statements. The former uses GetKeyDown(), while the latter uses GetKey() (and checks our bool). The distinction is that we should erase a character every time the user presses backspace, but not constantly while the user holds it down.

The coroutines BackspaceInitialHold() and () simply wait 0.15 and 0.05 seconds, respectively, and then set backspacepossible to true. So after our user has held down backspace for 0.15 seconds, as long as they're still holding backspace, a character will be erased every 0.05 seconds (as long as the length is greater than code>0).

Also, we stipulate that if this is the first button the user presses, initialpress is triggered (so it won't try to remove a character once they press something else).

To top it all off, we need to allow the user to press Return to finish the name input.

if (playerNameTemp.Length > 0 && initialpress)
{
    if (Input.GetKeyDown(KeyCode.Return))
    {
        foreach(GUIText text in FindObjectsOfType(typeof(GUIText)) as GUIText[])
        {
            text.guiText.enabled = false;
        }
        GetComponent<HighScore>().SetName(guiText.text);
        GetComponent<HighScore>().enabled = true;
        enabled = false;
    }
}

As long as the user has made some kind of input and the length is greater than 0, the name will be accepted. All our text objects get deleted, we disable this class, and we turn on our third class, HighScore. All three of our classes have to be put on our object in the editor.

We just called Highscore's SetName() function, and earlier we called SetScore(). Each of these functions simply sets the values of private variables that we'll submit now to our leaderboard.


Accessing Your Leaderboard in Unity

At the top of HighScore we want to declare some variables. First:

public GameObject BaseGUIText;

This is the GUIText prefab that we'll base our leaderboard on. You should make sure the text is anchored to the middle-left, and left-aligned. You can also choose a font here too.

private string privateKey = "THE KEY YOU GENERATED BEFORE";
private string AddScoreURL = "http://yoursite.com/AddScore.php?";
private string TopScoresURL = "http://yoursite.com/TopScores.php";
private string RankURL = "http://yoursite.com/GrabRank.php?";
private int highscore;
private string username;
private int rank;

We need all our values from before - the key you generated, the URLs you uploaded your PHP files to, and so on. The highscore and username variables are set using two public functions called SetScore() and SetName(), which we used in the previous section.

Tip: It's really important you put question marks after AddScore.php and GrabRank.php! These allow you to pass variables to your PHP files.

We need to use IEnumerators here to handle our SQL queries, because we need to wait for a response. We'll start our first coroutine, AddScore(), as soon as the class is enabled.

IEnumerator AddScore(string name, int score)
{

    string hash = Md5Sum(name + score + privateKey);

    WWW ScorePost = new WWW(AddScoreURL + "name=" + WWW.EscapeURL(name) + "&score=" + score + "&hash=" + hash);
    yield return ScorePost;

    if (ScorePost.error == null)
    {
        StartCoroutine(GrabRank(name));
    }
    else
    {
        Error();
    }
}

First, we create our hash with the private key, using a function to create an MD5 hash in the same way as PHP's md5(). There's an example of this on Unity's community wiki.

Here, if the server is inaccessible for any reason, we run an Error() function. You can choose what you want to occur in your error handler. If the score gets posted correctly, however, we'll launch our next coroutine: GrabRank().

IEnumerator GrabRank(string name)
{

    WWW RankGrabAttempt = new WWW(RankURL + "name=" + WWW.EscapeURL(name));

    yield return RankGrabAttempt;

    if (RankGrabAttempt.error == null)
    {
     rank = System.Int32.Parse(RankGrabAttempt.text);
        StartCoroutine(GetTopScores());

    }
    else
    {
        Error();
    }
}

Again, we access the site, and this time we store the rank if it's successfully taken.

Now we can use our last coroutine. This one will tie up everything. We start by accessing the URL for a final time:

IEnumerator GetTopScores()
{
    WWW GetScoresAttempt = new WWW(TopScoresURL);
    yield return GetScoresAttempt;

    if (GetScoresAttempt.error != null)
    {
        Error();
    }
    else
    {

But this time we want to split up the data we receive into an array of strings. First of all we can use a string split like so:

string[] textlist = GetScoresAttempt.text.Split(new string[]{"\n","\t"}, System.StringSplitOptions.RemoveEmptyEntries);

This will make sure each result will be a new element in our string array, as long as a new line or a tab is found (which it will be!). We're now going to split this into two further arrays called Names and Scores.

string[] Names = new string[Mathf.FloorToInt(textlist.Length/2)];
string[] Scores = new string[Names.Length];
for (int i = 0; i < textlist.Length; i++)
{
    if (i % 2 == 0)
    {
        Names[Mathf.FloorToInt(i / 2)] = textlist[i];
    }
    else Scores[Mathf.FloorToInt(i / 2)] = textlist[i];
}

We've now made two new arrays which are each half the size of the first array. We then split every first string into our Names array and every second one into our Scores array.

Now we want to show these as text, so we need to position our three columns. We're going to scale them to the screen, so they fit any resolution. First we'll declare the initial positions where our title text will go:

Vector2 LeftTextPosition = new Vector2(0.22f,0.85f);
Vector2 RightTextPosition = new Vector2(0.76f, 0.85f);
Vector2 CentreTextPosition = new Vector2(0.33f, 0.85f);

And now we're ready to create our text objects, based on our BaseGUIText prefab. We instantiate the titles individually and set their text - for instance:

GameObject Scoresheader = Instantiate(BaseGUIText, new Vector2(0.5f,0.94f), Quaternion.identity) as GameObject;
Scoresheader.guiText.text = "High Scores";
Scoresheader.guiText.anchor = TextAnchor.MiddleCenter;
Scoresheader.guiText.fontSize = 35;

Once we've done this for all our titles, we adjust our positions so that the new text will display lower down.

LeftTextPosition -= new Vector2(0, 0.062f);
RightTextPosition -= new Vector2(0, 0.062f);
CentreTextPosition -= new Vector2(0, 0.062f);

Next we run a for loop that will iterate through our whole top 10 list, setting the name, rank and score (and making sure the text is anchored sensibly), and then adjusting the positions yet again. Every iteration, we check whether the user's rank is equal to the rank displayed, and if so we recolour the text so that the user's score is highlighted in yellow:

for(int i=0;i<Names.Length;i++){
    GameObject Score = Instantiate(BaseGUIText, RightTextPosition, Quaternion.identity) as GameObject;
    Score.guiText.text = Scores[i];
    Score.guiText.anchor = TextAnchor.MiddleCenter;
    GameObject Name = Instantiate(BaseGUIText, CentreTextPosition, Quaternion.identity) as GameObject;
    Name.guiText.text = Names[i];
    GameObject Rank = Instantiate(BaseGUIText, LeftTextPosition, Quaternion.identity) as GameObject;
    Rank.guiText.text = "" + (i + 1);
    Rank.guiText.anchor = TextAnchor.MiddleCenter;
    if (i + 1 == rank)
    {
        Score.guiText.material.color = Color.yellow;
        Name.guiText.material.color = Color.yellow;
        Rank.guiText.material.color = Color.yellow;
    }
    LeftTextPosition -= new Vector2(0, 0.062f);
    RightTextPosition -= new Vector2(0, 0.062f);
    CentreTextPosition -= new Vector2(0, 0.062f);
}

And then, finally, we check whether the user's rank is above 10. If it is, we post their score at the bottom in the eleventh slot, along with their rank, and colour it yellow.

if (rank > 10)
{
    GameObject Score = Instantiate(BaseGUIText, RightTextPosition, Quaternion.identity) as GameObject;
    Score.guiText.text = ""+highscore;
    Score.guiText.anchor = TextAnchor.MiddleCenter;
    GameObject Name = Instantiate(BaseGUIText, CentreTextPosition, Quaternion.identity) as GameObject;
    Name.guiText.text = username;
    GameObject Rank = Instantiate(BaseGUIText, LeftTextPosition, Quaternion.identity) as GameObject;
    Rank.guiText.text = "" + (rank);
    Rank.guiText.anchor = TextAnchor.MiddleCenter;

    Score.guiText.material.color = Color.yellow;
    Name.guiText.material.color = Color.yellow;
    Rank.guiText.material.color = Color.yellow;
}

Conclusion

Voilà; our leaderboard is complete! In the source files I've also included a PHP file that will grab the ranks above and below the user's username, so you can show them exactly where they are on the board.

Advertisement