Adventures in Unity – 1.7a Highscores (Server Side MySQLi)

Official_unity_logo

When it came to setting up the high-scores for Project2 (Bounder),  I discovered that the server-side/back-end PHP I’d used for Project 1 (BlockRun) was now out of date.

It seems that from PHP 5.5.x? onward the MySQL extensions had been deprecated, instead MySQLi or PDO_MySQL were new new extension options.
The Unity3D wiki provides a PDO version which can be found here Server Side Highscores
I wanted to get my hands a little dirtier this time – So I instead opted to update the code to use MySQLi.

I hope, in this post, to give a quick overview of the amended code
I’ve also updated the project 1 file (zipped up and linked at the bottom of this article and every project 1 post) it now contains both the MySQL and the new MySQLi files)


There are four files used for the server-side high-scores

  1. .htaccess
  2. addscore.php
  3. crossdomain.xml
  4. display.php

Since .htaccess and crossdomain.xml are unchanged in this update, I won’t discuss them in this post – If you’d like to know more about what I’m doing (probably wrong) with these files, what they are for, and where to put them in your own high-score setup – try here: Adventures in Unity – 1.7a Highscores (Server Side)

This post will just cover the MySQLi implementation of addscore.php and display.php.


addscore.php

This is called by the game when adding a new high-score.

The full PHP (included in BlockRun.zip – link below);

$servername = "localhost";
$username = "username";
$password = "password";
$database = "database";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);

// Check connection
if (!$conn) {
	die("Connection failed: " . mysqli_connect_error());
}

//---

// Strings must be escaped to prevent SQL injection attack. 
$name = mysqli_real_escape_string($conn, $_GET['name']);
$score = mysqli_real_escape_string($conn, $_GET['score']);	
$hash = $_GET['hash']; 
	
//---
	
$secretKey="secretKey"; # Change this value to match the value stored in the client javascript below 
$real_hash = md5($name . $score . $secretKey);
	
if($real_hash == $hash) {
	echo "#1";
	
	// Send variables for the MySQL database class. 
	$query = "INSERT INTO BlockRun (name, score) VALUES ('$name', '$score')";
	$result=mysqli_query($conn,$query);
	mysqli_free_result($result); // Free result set
		
	//---
			
	$query = "delete from BlockRun Where id not in (select * from(select id from BlockRun order by score desc limit 50) as temp)"; 
	$result=mysqli_query($conn,$query);
	mysqli_free_result($result); // Free result set

} 
	
//---
	
echo "# END";
mysqli_close($conn);
	

Breaking this down;

First, the PHP attempts to connect to the database…

// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);

 

If no connection can be made, the script exits & throws an error…

// Check connection
if (!$conn) {
	die("Connection failed: " . mysqli_connect_error());
}

 

PHP collects the new highscores name & score as well as the hash provided by the game;

// Strings must be escaped to prevent SQL injection attack. 
$name = mysqli_real_escape_string($conn, $_GET['name']);
$score = mysqli_real_escape_string($conn, $_GET['score']);	
$hash = $_GET['hash']; 

 

We have a local copy of the secret key (this should match the key in the game code) and build a server side instance of the hash (this will be used to ensure the new highscore is valid)…

$secretKey="secretKey"; # Change this value to match the value stored in the client javascript below 
$real_hash = md5($name . $score . $secretKey);

 

If everything is valid…

if($real_hash == $hash)

 

The new high-score is added into the database…

$query = "INSERT INTO BlockRun (name, score) VALUES ('$name', '$score')";
$result=mysqli_query($conn,$query);
mysqli_free_result($result); // Free result set

 

Because only the top 50 highscores are stored, after adding a new entry the database is queried, with the query results ordered by score (highest to lowest) – deleting any scores from the 51st row onward…

$query = "delete from BlockRun Where id not in (select * from(select id from BlockRun order by score desc limit 50) as temp)"; 
$result=mysqli_query($conn,$query);
mysqli_free_result($result); // Free result set

 

With that done, the connection to the database is closed and the process ends…

echo "# END";
mysqli_close($conn);

addscore.php

This is called by the game when adding a new highscore.

The full PHP (included in BlockRun.zip – link below);

$servername = "localhost";
$username = "username";
$password = "password";
$database = "database";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);

// Check connection
if (!$conn) {
	die("Connection failed: " . mysqli_connect_error());
}

//---

$query = "SELECT * FROM `BlockRun` ORDER by `score` DESC LIMIT 50";

if ($result=mysqli_query($conn,$query))
{
	while ($row = mysqli_fetch_array($result)) {
		echo $row['name'] . "|" . $row['score'] . "\n";
	}		
}

//---

echo "#";
	
mysqli_free_result($result); // Free result set
mysqli_close($conn);	

Breaking this down;

 

First, the PHP attempts to connect to the database…

// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);

 

If no connection can be made, the script exits & throws an error…

// Check connection
if (!$conn) {
	die("Connection failed: " . mysqli_connect_error());
}

 

If a successful connection is made, a query is constructed requesting the top 50 scores are returned (I only store the top 50 scores in this database) – The results are returning in descending order (highest to lowest) based on the players score…

$query = "SELECT * FROM `BlockRun` ORDER by `score` DESC LIMIT 50";

 

The database is queried and the results are stored in $result
Each row in the results is then ‘echoed’ back to the game…

if ($result=mysqli_query($conn,$query))
{
	while ($row = mysqli_fetch_array($result)) {
		echo $row['name'] . "|" . $row['score'] . "\n";
	}		
}

 

A final output to confirm the end of the file…

echo "#";

 

Once complete everything is tided up – emptying the $result set and closing the connection…

mysqli_free_result($result); // Free result set
mysqli_close($conn);

Play the game (WebGL)

Grab a copy of the project here



Next post: 1.7b Highscores (In-Game Overview)

Last post: 1.7a Highscores (Server Side)

Contents page.


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s