Month: May 2020

Creating a simple Unity online leaderboard

Creating a simple Unity online leaderboard

This guide will show you how to create a simple highscore / leaderboard system in Unity, where users can submit and view each others’ scores. It also works on any platform!

Requirements:
– Unity game engine
– A web server
– A database
– Some coding knowledge in C#, php, SQL
(Don’t worry if you’re unfamiliar with any of the above, there are detailed steps below and the source code will be provided)
(You don’t have to pay for a web server or database, you can use the same free server used in this guide)

Setting up the database

For this guide, we will be using a free web server service, https://www.000webhost.com/ . If you have your own server with database capabilities, I highly recommend you use that instead, as 000webhost may clear your data if your web server isn’t used frequently. (You may also choose another free web server service, do let me know in the comments if you find any!)

Setup your account with the 000webhost service and navigate to the “manage website” page

You should be able to see a dashboard with several options. Select the MySQL Database option to begin setting up the database.

Click the “+New Database” button and fill in the required fields.

Once you have created the database, it should appear under “My Databases”. Make sure to write down your DB Name/User! We will need them later on. Select PhpMyAdmin under the “Manage” option.

Click the database with the name matching the one that you just created and click “New” to add a new table.

More info
PhpMyAdmin is used to interface with your database, and display its contents. We are using a MySQL database, which is a relational database which stores data in tables. Each table has multiple columns with their own properties, and entries(also called rows) can be added to the table.

Enter a table name that you like, remember to keep track of this as well! For our table columns, we are going to have an ID column, a name column, and a score column.

ID column
The ID column should have a type of “INT”, an attribute of “UNSIGNED”, and should have the “A_I” checkbox checked.

Explanation
This column helps us to identify every single score submitted with a unique id, so we can easily modify any row later on if we want to.
It stores an unsigned(positive) integer(whole number). We use unsigned integers as IDs do not make use of negative numbers. A signed integer can store negative and positive integers, but would only allow us to store half as many IDs as the negative numbers would not be used, therefore an unsigned integer would be a better choice in this case.
The “A_I” checkbox stands for auto increment, which automatically increases the ID by 1 everytime a new score is posted to the database

Name column
The name column should have a type of “CHAR”. Use the “Length/Values” box to specify the maximum length of a user’s name.

Explanation
This column stores the names of the users that submit their scores. We use a “CHAR” instead of other string types to limit the number of characters a user may enter for their name and to save storage space.

Score column
The score column should have a type of “INT” and an attribute of “UNSIGNED”.

Explanation
You may modify the type and and attribute depending on the needs of your leaderboard. If the range of possible scores is very small, you may choose to use a type of “MEDIUMINT” or “SMALLINT” to save storage space, or “FLOAT” if the scores require a float value. You may also choose to leave the attribute as the default “SIGNED” if your scores have negative values.

After adding all the columns, click on save to create the table. You should be presented with a page similar to this, which displays the columns in the table and their properties.

Creating the php page

Now that we have created our database, we need a webpage that communicates to the database to get or post scores.
Heading back to the 000webhost dashboard, select the “File Manager” and click the “Upload files” button.

This should being you to the file manager view where we can upload/edit the files in our web server.

We need to create a text file with the .php extension and place it in the “public_html” directory. For this step you may choose to create and edit the file directly in the file manager view of your browser, or do it using your own preferred editor and upload the file later on.

The first thing we need to do is specify the database we want to connect to.

Explanation
We need to specify the database to connect to by defining the host, database name, database user, and database password. Hopefully you wrote them down from the earlier steps!
Don’t forget to close your connection at the end so that we don’t hog the server resources.
// Define the database to connect to
define("DBHOST", "localhost");
define("DBNAME", "your database name here");
define("DBUSER", "your database user here");
define("DBPASS", "your database password here");

// Connect to the database
$connection = new mysqli(DBHOST, DBUSER, DBPASS, DBNAME);

// Other code goes here

// Close the database connection
$connection->close();

Next we check if the user is requesting for the scores in the leaderboard, or would like to post a new score to the leaderboard. We do this using a POST request. Currently we are only working on the receiving end of the request, the actual sending of the POST request will be done later in Unity.

If the request is to retrieve the leaderboard, we simply send a query to the MySQL database to retrieve the scores in descending order. Remember to update the “TABLENAME” to the one you used for your own table(which was created earlier on)

// Check if the request is to retrieve or post a score to the leaderboard
if (isset($_POST['retrieve_leaderboard']))
{
    // Create the query string
    $sql = "SELECT * FROM TABLENAME ORDER BY score DESC limit 50";

    // Execute the query
    $result = $conn->query($sql);
    $num_results = $result->num_rows;

    // Loop through the results and print them out, using "\n" as a delimiter
    for ($i = 0; $i < $num_results; $i++) 
    {
        if (!($row = $result->fetch_assoc()))
            break;
        echo $row["name"];
        echo "\n";
        echo $row["score"];
        echo "\n";
    }

    $result->free_result();
}

If the request is to post a new score to the leaderboard, we need to get the name and score of the user from the POST request.

Explanation
The posting of scores is a little different from just retrieving scores. Since the we will be sending data that the user has input, to the database, this may be prone to SQL injections.
To prevent this, we take the name the user has input and use php’s mysqli_escape_string and filter_var functions to clean the string. This adds an escape character “\” to any character that the database may misinterpret.
We then make use of prepared statements to send the SQL query to the MySQL database.
More information can be found here.
elseif (isset($_POST['post_leaderboard']))
{
    // Get the user's name and store it
    $name = mysqli_escape_string($connection, $_POST['name']);
    $name = filter_var($message, FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH);
    // Get the user's score and store it
    $score = $_POST['score'];

    // Create prepared statement
    $statement = $connection->prepare("INSERT INTO TABLENAME (name, score) VALUES (?, ?)");
    $statement->bind_param("si", $name, $score);

    $statement->execute();
    $statement->close();
}

The complete code should look something like this (with the database and table values updated with your own values). There should also be opening and closing php tags at the start and end of the code.

<?php

// Define the database to connect to
define("DBHOST", "localhost");
define("DBNAME", "your database name here");
define("DBUSER", "your database user here");
define("DBPASS", "your database password here");

// Connect to the database
$connection = new mysqli(DBHOST, DBUSER, DBPASS, DBNAME);

// Other code goes here
// Check if the request is to retrieve or post a score to the leaderboard
if (isset($_POST['retrieve_leaderboard']))
{
    // Create the query string
    $sql = "SELECT * FROM TABLENAME ORDER BY score DESC limit 50";

    // Execute the query
    $result = $conn->query($sql);
    $num_results = $result->num_rows;

    // Loop through the results and print them out, using "\n" as a delimiter
    for ($i = 0; $i < $num_results; $i++) 
    {
        if (!($row = $result->fetch_assoc()))
            break;
        echo $row["name"];
        echo "\n";
        echo $row["score"];
        echo "\n";
    }

    $result->free_result();
}
elseif (isset($_POST['post_leaderboard']))
{
    // Get the user's name and store it
    $name = mysqli_escape_string($connection, $_POST['name']);
    $name = filter_var($message, FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH);
    // Get the user's score and store it
    $score = $_POST['score'];

    // Create prepared statement
    $statement = $connection->prepare("INSERT INTO TABLENAME (name, score) VALUES (?, ?)");
    $statement->bind_param("si", $name, $score);

    $statement->execute();
    $statement->close();
}

// Close the database connection
$connection->close();

?>

Remember to save the file to the “public_html” directory of your web server, with the extention .php (E.g. highscore.php)

Making the request from Unity

We will be using Unity’s built in UnityWebRequest class to send our request.

Make sure to update the “highscoreURL” string to match your own web server’s url.

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.Networking;
using System.IO;
using System;

public class HighscoreHandler : MonoBehaviour
{
    private const string highscoreURL = "http://your-website-url-here.com/highscore.php";

    public List<Score> RetrieveScores()
    {
        List<Score> scores = new List<Score>();
        StartCoroutine(DoRetrieveScores(scores));
        return scores;
    }

    public void PostScores(string name, int score)
    {
        StartCoroutine(DoPostScores(name, score));
    }

    IEnumerator DoRetrieveScores(List<Score> scores)
    {
        WWWForm form = new WWWForm();
        form.AddField("retrieve_leaderboard", "true");

        using (UnityWebRequest www = UnityWebRequest.Post(highscoreURL, form))
        {
            yield return www.SendWebRequest();

            if (www.isNetworkError || www.isHttpError)
            {
                Debug.Log(www.error);
            }
            else
            {
                Debug.Log("Successfully retrieved scores!");
                string contents = www.downloadHandler.text;
                using (StringReader reader = new StringReader(contents))
                {
                    string line;
                    while ((line = reader.ReadLine()) != null)
                    {
                        Score entry = new Score();
                        entry.name = line;
                        try
                        {
                            entry.score = Int32.Parse(reader.ReadLine());
                        }
                        catch (Exception e)
                        {
                            Debug.Log("Invalid score: " + e);
                            continue;
                        }

                        scores.Add(entry);
                    }
                }
            }
        }
    }

    IEnumerator DoPostScores(string name, int score)
    {
        WWWForm form = new WWWForm();
        form.AddField("post_leaderboard", "true");
        form.AddField("name", name);
        form.AddField("score", score);

        using (UnityWebRequest www = UnityWebRequest.Post(highscoreURL, form))
        {
            yield return www.SendWebRequest();

            if (www.isNetworkError || www.isHttpError)
            {
                Debug.Log(www.error);
            }
            else
            {
                Debug.Log("Successfully posted score!");
            }
        }
    }
}

public struct Score
{
    public string name;
    public int score;
}

To use the script, attach it to any GameObject and call the “RetrieveScores” and “PostScores” functions to retrieve and post your scores respectively!

To display the highscores retrieved from “RetrieveScores”, simply loop through the scores and write them to a Text UI object.

An example of a game which makes use of this leaderboard can be found here:


This game was made during the Ludum Dare 46 game jam by me!

Questions you might have

Q. Why don’t I just make the query to the MySQL database directly from my Unity C# code?
Making the query directly from Unity would mean that you would have to store your database credentials inside your Unity code. Even after compiling your game/application, it is still relatively easy to reverse engineer it and have your credentials stolen. Making the request from a php web page is more secure as the credentials are stored on your web server, blocking the public from having any access.

Q. The leaderboard isn’t working with a WebGL build of my game/application!
If your WebGL game is hosted on a different website such as itch.io, your web server will block Cross-origin requests. To fix this add the following code to the beginning of your php file to allow that particular website access:

//Insert the domain your game is hosted on into the array
$cors_whitelist = [
  'https://itch.io'
];

if (in_array($_SERVER['HTTP_ORIGIN'], $cors_whitelist)) 
{
    header('Access-Control-Allow-Origin: ' . $_SERVER['HTTP_ORIGIN']);
}

Q. Something doesn’t work!
Leave a comment below and i’ll try to get back to you as soon as possible!

Thank you!

Thanks for reading my tutorial! Please leave any feedback you may have at all in the comments!