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)