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
- .htaccess
- addscore.php
- crossdomain.xml
- 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);
Grab a copy of the project here
Next post: 1.7b Highscores (In-Game Overview)
Last post: 1.7a Highscores (Server Side)
10 Print "Hello World" 