Setting up a leaderboard using PHP/MySQL

Games are more fun when there is a leaderboard of high scores that players have achieved. Even single-player games can communicate to a shared web-based leaderboard. This recipe includes both the client-side (Unity) code, as well as the web server-side PHP scripts to set and get player scores from a MySQL database, as seen in the following screenshot:

Setting up a leaderboard using PHP/MySQL

Getting ready

This recipe assumes you either have your own web hosting, or are running a local web server and database server such as XAMPP or MAMP. Your web server needs to support PHP, and you also need to be able to create MySQL databases.

All the SQL, PHP, and C# scripts for this recipe can be found in the 0423_07_10 folder.

Note

If you are hosting your leaderboard on a public website, you should change the names of the database, database user, and password for security reasons. You should also implement some form of secret game code, as described in the There's more… section.

How to do it...

  1. On your server, create a new MySQL database named cookbook_highscores.
  2. On your server, create a new database user (username=cookbook. password=cookbook), with full rights to the database you just created.
  3. On your server, execute the following SQL to create the score_list database table:
    CREATE TABLE 'score_list' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'player' varchar(25) NOT NULL,
  'score' int(11) NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
  4. On your server, copy these provided PHP script files to your web server:
    • index.php
    • scoreFunctions.php
    • htmlDefault.php
  5. Add the following script class to the Main Camera:
    // file: WebLeaderBoard.cs
using UnityEngine;
using System.Collections;
using System;

public class WebLeaderBoard : MonoBehaviour {
  private string url;
  private string action;
  private string parameters;
  private string textFileContents = "(still loading file ...)";

  private void OnGUI() {
    // hide closing tag
    string prettyText = textFileContents.Replace("</", "?@?"); 
    
    // prefix opening tag with newline
    prettyText = prettyText.Replace("<", "
<"); 
    
    // return closing tag 
    prettyText = prettyText.Replace("?@?", "</"); 

    GUILayout.Label ( "last url = " + url );
    GUILayout.Label ( StringToInt(textFileContents) );
    GUILayout.Label ( "results from last url = " + prettyText );
    
    WebButtons();
  }
  
  private void WebButtons() {
    bool getButtonWasClicked = GUILayout.Button("Get score for player 'matt'");
    bool setButtonWasClicked = GUILayout.Button("Set score for player 'matt' to random integer 500-510");
    bool htmlButtonWasClicked = GUILayout.Button("Get html for all players");
    bool xmlButtonWasClicked = GUILayout.Button("Get xml for all players");
    bool resetButtonWasClicked = GUILayout.Button("Reset all scores");
    
    if( getButtonWasClicked )
      GetAction();
    if( setButtonWasClicked )
      SetAction();
    if( htmlButtonWasClicked )
      HTMLAction();
    if( xmlButtonWasClicked )
      XMLAction();
    if( resetButtonWasClicked )
      ResetAction();
  }
  
  private string StringToInt(string s) {
    string intMessage = "integer received = ";
    try{
      int integerReturned = Int32.Parse(s);
      intMessage += integerReturned;
    }
    catch(System.Exception e){
      intMessage += "(not an integer) ";
      print (e);
    }
    return intMessage;
  }
  
  private void GetAction() {
    action = "get";
    parameters = "&player=matt";
    StartCoroutine( LoadWWW() );
  }

  private void SetAction() {
    int randomScore = UnityEngine.Random.Range(500, 510);
    parameters = "&player=matt&score=" + randomScore;
    action = "set";
    StartCoroutine( LoadWWW() );
  }

  private void HTMLAction() {
    action = "html";
    parameters = "";
    StartCoroutine( LoadWWW() );
  }

  private void XMLAction() {
    action = "xml";
    parameters = "";
    StartCoroutine( LoadWWW() );
  }

  private void ResetAction() {
    action = "reset";
    parameters = "";
    StartCoroutine( LoadWWW() );
  }

  private IEnumerator LoadWWW(){
    string baseUrl = "http://localhost/leaderboard/index.php?action=";
    url = baseUrl + action + parameters;
    WWW www = new WWW (url);
    yield return www;
      textFileContents = www.text;
  }
}

How it works...

The player's scores are stored in a MySQL database. Access to the basis is facilitated through the PHP scripts provided. In our example, all the PHP scripts were placed into a folder named leaderboard in the web server root folder. The scripts are accessed via the http://localhost/leaderboard/ URL. All access is through the index.php PHP file. There are five actions implemented, and each is indicated by adding the action name at the end of the URL (this is the GET HTTP method, sometimes used for web forms—take a look at the address bar of your browser next time you search Google, for example). The actions and their parameters (if any) are as follows:

  • action = get, parameters: player = matt:
    • This action asks for the integer score of the named player to be found
    • Returns: score integer
  • action = set, parameters: player = matt, score = 101:
    • This action asks for the provided score of the named player to be stored in the database (but only if this new score is greater than the currently stored score)
    • Returns: score integer (if database update was successful), otherwise a negative value (to indicate no update took place)
  • action = html, no parameters:
    • This action asks for HTML text listing all player scores to be returned
    • Returns: HTML text
  • action = xml, no parameters:
    • This action asks for XML text listing all player scores to be returned
    • Returns: XML text
  • action = reset, no parameters:
    • This action asks for a set of default player name and score values to replace the current contents of the database table
    • Returns: the reset string

The OnGUI() method first displays the current URL string, any integer value that was extracted from the response message received from the server, and the full text string received from the server. Also, five buttons are offered to the user, which set up the corresponding action and parameters to be added to the URL for the next call to the web server via the LoadWWW() method.

There's more...

Here is some information on how to fine-tune and customize this recipe.

Extract the full leaderboard data as XML for display within Unity

The XML text that can be retrieved from the PHP web server provides a useful method of allowing a Unity game to retrieve the full set of leaderboard data from the database. This allows the leaderboard to be displayed to the user in the Unity game (perhaps in some nice 3D fashion, or through a game-consistent GUI). See the next chapter for several recipes that illustrate ways to work with XML data in Unity.

Using secret game codes to secure your leaderboard scripts

The Unity and PHP code presented illustrates a simple, unsecured web-based leaderboard. To prevent players "hacking" into the board with false scores, it is common to encode some form of secret game code (or "key") into the communications. Only update requests that include the correct code will actually cause a change to the database.

The Unity code would combine the secret key (in this example, the harrypotter string), with something related to the communication. For example, the same MySQL/PHP leaderboard may have different database records for different games, identified with a game ID:

// Unity Csharp code
string key = "harrypotter"
string gameId = 21;
string gameCode = Utility.Md5Sum(key + gameId);

The server-side PHP code would receive both the encrypted game code, and also the piece of game data used to create that encrypted code (in this example, the game ID, and the MD5 hashing function, which is available both in Unity and in PHP). The secret key (harrypotter) is used with the game ID to create an encrypted code that can be compared with the code received from the Unity game (or whatever user agent or browser is attempting to communicate with the leaderboard server scripts). Database actions will only be executed if the game code created on the server matches that sent along with the request for a database action:

// PHP – security code
$key = "harrypotter"
$game_id =  $_GET['game_id'];
$provided_game_code =  $_GET['game_code'];
$server_game_code = md5($key.$game_id);

if( $server_game_code == $provided_game_code ) {
  // codes match - do processing here
}

See also

  • Loading and parsing external XML files in Chapter 8, Working with External Text Files and XML Data.
  • Preventing your game from running on unknown servers in Chapter 10, Improving Games with Extra Features and Optimization.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.16.212.217