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
