indiegamedev

Unity 3D – Server Side Highscores – JS Programming

How to manage server side highscores with Unity3D, PHP and MySQL.

Create a Data Base

Blue Host users:

CPanel> Database Tools> MySQL databases>

– Create a Database:
name -> game_scores
rules-> Collation

– Create a User

– Assign User to Database

CREATE TABLE ‘scores’

Blue Host users:

CPanel> phpMyAdmin> Enter with username and password

LEFT COLUMN> you will see your new empty Database.

A Database is a data structure with tables, every table has rows and columns.

TOP LABELS> SQL, here we can write the next SQL Query:

CREATE TABLE 'scores' (
   'id' INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   'name' VARCHAR(15) NOT NULL DEFAULT 'anonymous',
   'score' INT(10) UNSIGNED NOT NULL DEFAULT '0'
)
TYPE=MyISAM;

If you get an SQL Syntax error, replace TYPE=MyISAM; by ENGINE=MyISAM; as TYPE is deprecated.

If you get others syntax errors you can: phpMyAdmin> LEFT COLUMN> Crea Tabella +> Create the table

We will create:

id
– it is our index
– it is an integer number from 0 to 9999999999
– it is UNSIGNED, it means that can’t be negative (a SIGNED integer can hold both positive and negative numbers)
– it can’t be NULL (NULL value is different from zero, it means no value)
– it have an autoincrement of 1 unit
– it is our primary key, the primary key of a relational table uniquely identifies each record in the table.

name
– it is the nickname of our player
– it can be a string of 15 characters
– it can’t be NULL
– the default value will be ‘anonymous’, if the user does not type a nickname

score
– it is the score value
– it is an integer number from 0 to 9999999999
– it is UNSIGNED, it means that can’t be negative (a SIGNED integer can hold both positive and negative numbers)
– it can’t be NULL (NULL value is different from zero, it means no value)
– the default value is zero

addscore.php

Create into your server the script:

<?php 
        // Create connection
        // Statement: mysqli_connect(host,username,password,dbname)
        // NOTICE: se lo script è installato nello stesso server del Data Base, host->localhost 
        $db = mysql_connect('mysql_host', 'mysql_user', 'mysql_password') or die('Could not connect: ' . mysql_error()); 
        mysql_select_db('my_dbname') or die('Could not select database');
  
        // Strings must be escaped to prevent SQL injection attack. 
        $name = mysql_real_escape_string($_GET['name'], $db); 
        $score = mysql_real_escape_string($_GET['score'], $db);  
  
        // Send variables for the MySQL database class. 
        $query = "INSERT INTO scores VALUES (NULL, '$name', '$score');"; 
        $result = mysql_query($query) or die('Query failed: ' . mysql_error());     
?>

For italian people: come funziona?

1. Apro una connessione con il database fornendo i dati di accesso – ‘mysql_host’, ‘mysql_user’, ‘mysql_password’) –

2. Se la connessione è andata a buon fine non viene restituito alcun messaggio, se fallisce – die(‘Could not select database’) – viene restituito un messaggio di errore.

3. Con il comando – $_GET – ricevo i dati POST inviati dal gioco che vengono immagazzinati nelle variabili $name, $score

4. Per evitare l’hacking del database – mysql_real_escape_string() – non aggiunge le sequenze di escape a % ed a _.
In questo modo i malintenzionati non potranno operare ‘SQL injection’ inserendo codice maligno all’interno di una query SQ.

5. Inserisce nella tabella ‘scores’ i valori ‘$name’ e ‘$score’

6. Se fallisce restituisce il messaggio ‘Query failed’.

display.php

This script will take the top 5 scores from the MySQL Database, Unity3D will read the render of this script and put it into a GUIText.

<?php
 
    // To change the total number of row you need only change LIMIT 5
    // Example: to see the top ten set LIMIT 10
 
    // Connect to database
    // NOTICE: se lo script è installato nello stesso server del Data Base, mysql_host->localhost 
    $database = mysql_connect('mysql_host', 'mysql_user', 'mysql_password') or die('Could not connect: ' . mysql_error());
    mysql_select_db('my_dbname') or die('Could not select database');
  
    // Send a query, order the records in descending 
    $query = "SELECT * FROM scores ORDER BY score DESC LIMIT 5";
    // Store the result inside a variable or If fails send an error message
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());
  
    // How many rows there are inside the result
    $num_results = mysql_num_rows($result);  
   
    // Loop 5 times, remember some line above that LIMIT 5
    for($i = 0; $i < $num_results; $i++)
    {
         $row = mysql_fetch_array($result);
         // Render the result in text, questo viene letto poi da Unity3D come testo e renderizzato su un GUIText
         echo $row['name'] . "\t" . $row['score'] . "\n";
    }
?>

Now, we are going to populate the database to test our .php scripts.
1. phpMyAdmin> SQL label> write

/* Insert New Records in a table */ 
INSERT INTO scores (name, score)
VALUES ('Maria', '1260');

2. BOTTOM RIGHT> press ‘Esegui’ button
3. Insert 10 records and try display.php

XML – crossdomain.xml

Unity3D can send a WWW Request to a server only if it have a cross domain policy.
The crossdomain.xml file is a cross-domain policy file, it grants at the game the permission to talk to server, even if the game is not hosted inside it.

Upload the next file to the root of your web server, for BlueHost user put it into www. folder

crossdomain.xml:

<?xml version="1.0"?>
<cross-domain-policy>
<allow-access-from domain="*"/>
</cross-domain-policy>

Blue Host users: inside crossdomain.xml if you have secure=”true” only requests from HTTPS will be allowed, secure=”false” mean requests from both HTTP and HTTPS are allowed.

<?xml version="1.0"?>
<cross-domain-policy>
<allow-access-from domain="*" secure="false"/>
</cross-domain-policy>

For italian people: come funziona?
Il server deve essere settato per poter dialogare con applicazioni esterne, intese come applicazioni non installate direttamente all’interno del server stesso.
Il caso più ecclatante è quello di un gioco installato su Smartphone che deve inviare dei dati ad un server.
Anche un webgame che funziona su Unity Player è di fatto installato in locale sul PC del giocatore, nel momento in cui invia i punteggi al server viene visto dal servizio di hosting come un’applicazione esterna.

Potremo vedere alcuni esempio di sintassi per capire meglio.
Nel file sotto garantiamo il dialogo dalle richieste provenienti solo da domini specifici.

<?xml version="1.0"?>
<cross-domain-policy>
    <allow-access-from domain="*.mycompany.com" />
    <allow-access-from domain="*.speedtest.net" />
</cross-domain-policy>

Unity – Display Scores – JS

Open Unity 3D and create:
– Main Camera -> name it ‘Main Camera’
– GUI Text -> name it ‘Scores-Text’
– Empty Object -> name it ‘GameController’
– JS Script -> name it ‘HSController.js’, attach it to ‘GameController’

#pragma strict

var scoreText : GUIText; // Assign into Inspector the GUI Text you have created

// CHANGE THIS VALUE WITH YOUR ADDRESS
var urlDisplay = "http://www.lucedigitale.com/testgames/display.php";
  
function Start() {
    getScores(); // get and display the scores into GUIText scoreText
}
// Get Score START ###################################################################  
// Get the scores from the MySQL DB to display in a GUIText.
function getScores() {
    // First a loading message
    scoreText.text = "Loading Scores";
    // Start a download of the given URL
    var wwwDisplay : WWW = new WWW (urlDisplay);
    // Wait for download to complete
    yield wwwDisplay;
    // if it can't load the URL
    if(wwwDisplay.error) {
        // Write in the console: There was an error getting the high score: Could not resolve host: xxx; No data record of requested type
        print("There was an error getting the high score: " + wwwDisplay.error);
        // Display an error message
        scoreText.text = "No Data Record";
    } else {
        // This is a GUIText that will display the scores in game
        scoreText.text = wwwDisplay.text; 
    }
}
// Get Score END ###################################################################  

The result will be:

Maria 2999
Giovanna 1787
Arianna 87
Antonio 24
Erica 12

Unity – Display and Write Scores – JS

Ok, now we are going to add functions to write scores

#pragma strict

var scoreText : GUIText; // Assign into Inspector the GUI Text you have created

// CHANGE THIS VALUE WITH YOUR ADDRESS TO GET BEST SCORES
var urlDisplay = "http://www.lucedigitale.com/testgames/display.php";
// CHANGE THIS VALUE WITH YOUR ADDRESS TO WRITE SCORES
var urlAddScores = "http://www.lucedigitale.com/testgames/addscore.php";

// CHANGE THIS VALUES WITH YOUR OWN
// this vars are private because we won't put data from Inspector, public will cause a refresh error 
private var playerName : String = "Andrea"; // name of the player
private var playerScore : int = 56565656; // the players' score
  
function Start() {
    getScores(); // get and display the scores into GUIText scoreText
}
// Get Score START ###################################################################  
// Get the scores from the MySQL DB to display in a GUIText.
function getScores() {
    // First a loading message
    scoreText.text = "Loading Scores";
    // Start a download of the given URL
    var wwwDisplay : WWW = new WWW (urlDisplay);
    // Wait for download to complete
    yield wwwDisplay;
    // if it can't load the URL
    if(wwwDisplay.error) {
        // Write in the console: There was an error getting the high score: Could not resolve host: xxx; No data record of requested type
        print("There was an error getting the high score: " + wwwDisplay.error);
        // Display an error message
        scoreText.text = "No Data Record";
    } else {
        // This is a GUIText that will display the scores in game
        scoreText.text = wwwDisplay.text; 
    }
} // END getScores()
// Get Score END ###################################################################  

// Write Score START ###############################################################

// Button to send scores START
function OnGUI () {
    if (GUI.Button (Rect (10,10,350,100), "Send player name and Scores:" + playerName + " " + playerScore)) {
        // send datas to function to POST scores
        postScore(playerName, playerScore);
    }
} // END OnGUI
// Button to send scores END 

function postScore(name, score) {
     // Debug code to verify datas on console
     Debug.Log("Name " + name + " Score " + score);
     
    //This connects to a server side php script that will add the name and score to a MySQL DB.
    // Supply it with a string representing the players name and the players score.
    // Once it will generate an error message of Implicit Downcast, no problem it will work well
    var postData : String = urlAddScores + "?name=" + WWW.EscapeURL(name) + "&score=" + score;
    // Debug Code
    Debug.Log(postData);
    // It sends: http://www.lucedigitale.com/testgames/addscore.php?name=Andrea&score=123321
    // notare che il nome delle variabili in POST devono essere uguali a quelli in GET di addscore.php
    var wwwPostScore : WWW = new WWW (postData);
    // Wait until the post is done
    yield wwwPostScore; 
    // If it can't give an errore message
    if(wwwPostScore.error) {
        // Debug code
        print("There was an error posting the high score: " + wwwPostScore.error);
        // Error message for the player
        scoreText.text = "I can't record data";
    }
}// END postScore()

// Write Score END ################################################################# 

addscore.php – update existing player

Now I can improve my .php script, updating existing players and closing db connection.

<?php 
        // Create connection
        // Statement: mysqli_connect(host,username,password,dbname)
        // NOTICE: se lo script è installato nello stesso server del Data Base, host->localhost 
        $db = mysql_connect('mysql_host', 'mysql_user', 'mysql_password') or die('Could not connect: ' . mysql_error()); 
        mysql_select_db('mysql_dbname') or die('Could not select database');
  
        // Strings must be escaped to prevent SQL injection attack. 
        $name = mysql_real_escape_string($_GET['name'], $db); 
        $score = mysql_real_escape_string($_GET['score'], $db);  
  
        // Check if the name already exists
		$checkname = mysql_query("SELECT 1 FROM scores WHERE name='$name' LIMIT 1");
		// if exists
		if (mysql_fetch_row($checkname)) {
		        // Update the existing name with new score
			// AGGIORNA db_name SETTA il valore di score dove name è uguale a quello ottenuto con GET
			$queryupdate = "UPDATE scores SET score=$score WHERE name='$name'"; 	
			$resultupdate = mysql_query($queryupdate) or die('Query failed: ' . mysql_error()); 		
		// if not exists
		} else {
		        // Insert a new name and a new score 
			$query = "INSERT INTO scores VALUES (NULL, '$name', '$score');"; 
			$result = mysql_query($query) or die('Query failed: ' . mysql_error()); 
		}
		
		// Close the connection with the database
		mysqli_close($db); 
        echo "<br>Great! Connection Closed!"; 
?>

addscore.php – update existing player – write only best score

I will write in the database only if new score is better than the older one.

<?php 
        // Create connection
        // Statement: mysqli_connect(host,username,password,dbname)
        // NOTICE: se lo script è installato nello stesso server del Data Base, host->localhost 
        $db = mysql_connect('mysql_host', 'mysql_user', 'mysql_password') or die('Could not connect: ' . mysql_error()); 
        mysql_select_db('mysql_dbname') or die('Could not select database');
  
        // Strings must be escaped to prevent SQL injection attack. 
        $name = mysql_real_escape_string($_GET['name'], $db); 
        $score = mysql_real_escape_string($_GET['score'], $db);  
		
		// Check if the name already exists 
		$checkname = mysql_query("SELECT 1 FROM scores WHERE name='$name' LIMIT 1");
		
		// ------------------------------------------------
		// if exists --------------------------------------
		//-------------------------------------------------
		if (mysql_fetch_row($checkname)) {	
		echo "Vecchio giocatore";// Debug Code
		echo "<br>";// Debug Code
	        echo "Punteggio arrivato dal gioco: ".$score;// Debug Code
		echo "<br>";// Debug Code
                // Check score from database
		$checkscore = mysql_query("SELECT score FROM scores WHERE name='$name'");
		$checkscorerow = mysql_fetch_array($checkscore);
		echo "Punteggio ottenuto dal database: ".$checkscorerow['score'];// Debug Code
		
				// if the new score are better than old one
				if ($score > $checkscorerow['score']){
					echo "<br>Great! New personal record";
					
					// Update the existing name with new score
					// AGGIORNA db_name SETTA il valore di score dove name è uguale a quello ottenuto con GET
					$queryupdate = "UPDATE scores SET score=$score WHERE name='$name'";     
					$resultupdate = mysql_query($queryupdate) or die('Query failed: ' . mysql_error());
					
					mysqli_close($db); // Close the connection with the database
					echo "<br>Connection Closed!"; 
					break; // stop the execution of the script
				} else {
					echo "<br>Bad! Are you tired?";
					mysqli_close($db); // Close the connection with the database
					echo "<br>Connection Closed!"; 
					break; // stop the execution of the script
				}	
         
		// ------------------------------------------------
		// if not exists ----------------------------------
		// ------------------------------------------------
		} else {
			    echo "Nuovo giocatore";// Debug Code
		        // Insert a new name and a new score 
				$query = "INSERT INTO scores VALUES (NULL, '$name', '$score');"; 
				$result = mysql_query($query) or die('Query failed: ' . mysql_error()); 
		}		
		
		mysqli_close($db); // Close the connection with the database
        echo "<br>Connection Closed!"; 
?>

Ok, now we can add an MD5 encryption.

MD5 Encryption – HSController.js

#pragma strict

var scoreText : GUIText; // Assign into Inspector the GUI Text you have created

// CHANGE THIS VALUE WITH YOUR ADDRESS TO GET BEST SCORES
var urlDisplay = "http://www.lucedigitale.com/testgames/display.php";
// CHANGE THIS VALUE WITH YOUR ADDRESS TO WRITE SCORES
var urlAddScores = "http://www.lucedigitale.com/testgames/addscore.php";

// CHANGE THIS VALUES WITH YOUR OWN
// this vars are private because we won't put data from Inspector, public will cause a refresh error 
private var playerName : String = "AndreaTonin"; // name of the player
private var playerScore : int = 800; // the players' score
private var secretKey : String = "MyKey"; // the secret key to improve encryption  

private var secretSum : String; // the sum of playerName+playerScore+secretKey
private var secretSumMd5 : String; // the MD5 sum of: playerName+playerScore+secretKey
  
function Start() {
    getScores(); // get and display the scores into GUIText scoreText
    
    // calculate MD5 Key
    secretSum = playerName + playerScore + secretKey;
    secretSumMd5 = Md5Sum(secretSum);
}
// Get Score START ###################################################################  
// Get the scores from the MySQL DB to display in a GUIText.
function getScores() {
    // First a loading message
    scoreText.text = "Loading Scores";
    // Start a download of the given URL
    var wwwDisplay : WWW = new WWW (urlDisplay);
    // Wait for download to complete
    yield wwwDisplay;
    // if it can't load the URL
    if(wwwDisplay.error) {
        // Write in the console: There was an error getting the high score: Could not resolve host: xxx; No data record of requested type
        print("There was an error getting the high score: " + wwwDisplay.error);
        // Display an error message
        scoreText.text = "No Data Record";
    } else {
        // This is a GUIText that will display the scores in game
        scoreText.text = wwwDisplay.text; 
    }
} // END getScores()
// Get Score END ###################################################################  

// Write Score START ###############################################################
// Button to send scores START
function OnGUI () {
    if (GUI.Button (Rect (10,10,650,100), "SEND name score md5:" + playerName + " " + playerScore + " " + secretSumMd5)) {
        // send datas to function to POST scores
        postScore(playerName, playerScore,secretSumMd5);
    }
} // END OnGUI
// Button to send scores END 

function postScore(name, score, md5key) {
     // Debug code to verify datas on console
     Debug.Log("Name " + name + " Score " + score + " MD5Key " + md5key);
     
    //This connects to a server side php script that will add the name and score to a MySQL DB.
    // Supply it with a string representing the players name and the players score.
    // Once it will generate an error message of Implicit Downcast, no problem it will work well
    var postData : String = urlAddScores + "?name=" + WWW.EscapeURL(name) + "&score=" + score + "&md5key=" + md5key;
    // Debug Code
    Debug.Log(postData);
    // It sends: http://www.lucedigitale.com/testgames/addscore.php?name=Andrea&score=123321
    // notare che il nome delle variabili in POST devono essere uguali a quelli in GET di addscore.php
    var wwwPostScore : WWW = new WWW (postData);
    // Wait until the post is done
    yield wwwPostScore; 
    // If it can't give an errore message
    if(wwwPostScore.error) {
        // Debug code
        print("There was an error posting the high score: " + wwwPostScore.error);
        // Error message for the player
        scoreText.text = "I can't record data";
    }
}// END postScore()
// Write Score END ################################################################# 

// MD5 Encrytpt START ############################################################### 
// Server side note: the output is the same of the PHP function - md5($myString) -
static function Md5Sum(strToEncrypt: String)
{
	var encoding = System.Text.UTF8Encoding();
	var bytes = encoding.GetBytes(strToEncrypt);
 
	// encrypt bytes
	var md5 = System.Security.Cryptography.MD5CryptoServiceProvider();
	var hashBytes:byte[] = md5.ComputeHash(bytes);
 
	// Convert the encrypted bytes back to a string (base 16)
	var hashString = "";
 
	for (var i = 0; i < hashBytes.Length; i++)
	{
		hashString += System.Convert.ToString(hashBytes[i], 16).PadLeft(2, "0"[0]);
	}
 
	return hashString.PadLeft(32, "0"[0]);
}// End Md5Sum
// MD5 Encrypt END #####################################################################

MD5 Encryption – addscore.php

<?php 
        // Create connection
        // Statement: mysqli_connect(host,username,password,dbname)
        // NOTICE: se lo script è installato nello stesso server del Data Base, host->localhost 
        $db = mysql_connect('localhost', 'lucedigi_testgam', '3dmaster1508%A') or die('Could not connect: ' . mysql_error()); 
        mysql_select_db('lucedigi_testgames') or die('Could not select database');
  
        // GET post data from Unity3D
		// Strings must be escaped to prevent SQL injection attack. 
        $name = mysql_real_escape_string($_GET['name'], $db); 
        $score = mysql_real_escape_string($_GET['score'], $db);  
		$md5key = mysql_real_escape_string($_GET['md5key'], $db); 
		
		$secretKey = "MyKey"; // It is the same Unity3D posts
		$secretSum = $name.$score.$secretKey;
 
        // ------------------------------------------------
		// if MD5 Key is right ----------------------------
		//-------------------------------------------------
	if (md5($secretSum) === $md5key) {
        echo "Yes! It is the right MD5, let's write on the database";
		
		// Check if the name already exists 
		$checkname = mysql_query("SELECT 1 FROM scores WHERE name='$name' LIMIT 1");
		
		// ------------------------------------------------
		// if exists --------------------------------------
		//-------------------------------------------------
		if (mysql_fetch_row($checkname)) {	
		echo "<br>Old Player";// Debug Code
		echo "<br>";// Debug Code
	    echo "Punteggio arrivato dal gioco: ".$score;// Debug Code
		echo "<br>";// Debug Code
		$checkscore = mysql_query("SELECT score FROM scores WHERE name='$name'");
		$checkscorerow = mysql_fetch_array($checkscore);
		echo "Punteggio ottenuto dal database: ".$checkscorerow['score'];// Debug Code
		
				// if the new score are better than old one
				if ($score > $checkscorerow['score']){
					echo "<br>Great! New personal record";
					
					// Update the existing name with new score
					// AGGIORNA db_name SETTA il valore di score dove name è uguale a quello ottenuto con GET
					$queryupdate = "UPDATE scores SET score=$score WHERE name='$name'";     
					$resultupdate = mysql_query($queryupdate) or die('Query failed: ' . mysql_error());
					
					mysqli_close($db); // Close the connection with the database
					echo "<br>Connection Closed!"; 
					break; // stop the execution of the script
				} else {
					echo "<br>Bad! Are you tired?";
					mysqli_close($db); // Close the connection with the database
					echo "<br>Connection Closed!"; 
					break; // stop the execution of the script
				}	
         
		// ------------------------------------------------
		// if not exists ----------------------------------
		// ------------------------------------------------
		} else {
			    echo "Nuovo giocatore";// Debug Code
		        // Insert a new name and a new score 
				$query = "INSERT INTO scores VALUES (NULL, '$name', '$score');"; 
				$result = mysql_query($query) or die('Query failed: ' . mysql_error()); 
		}		
		
		mysqli_close($db); // Close the connection with the database
        echo "<br>Connection Closed!"; 
		
	} else {
		// Debug Code
		echo "Bad MD5! Who are you?";
		echo "<br>Data received: ".$name." ".$score." ".$md5key;
		echo "<br>MD5 calcolato dal server: ".md5($secretSum);
		break;
	}
?>

My official website: http://www.lucedigitale.com
Original article: http://wiki.unity3d.com/index.php?title=Server_Side_Highscores