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

Adventures in Unity – 2.11 Enemies, Bonuses & Environmental Spectrum

Official_unity_logo

Below is a (hopefully) complete list of NPC’s (i.e. everything that’s not a floor tile) used in the Spectrum version of Bounder.


Enemies

HandDisc
Type: Boxing Glove
Effect: Instant Death
Movement Type: Vertical (up & down), Down
Levels (no. in level): 2(1) 6(1)


 

BearTrap2
Type: BearTrap
Effect: Instant Death
Movement Type: Static, Horizontal (left to right)
Levels (no. in level): 3(1) 4(3) 5(2) 6(3) 9(2) 10(4)


Binary
Type: Binary
Effect: Instant Death
Movement Type: Vertical (up & down)
Levels (no. in level): 5(1)


Binoculars

BinocularsR
Type: Binoculars
Effect: Instant Death
Movement Type: Horizontal (left to right), Vertical (up & down)
Levels (no. in level): 1(3) 2(8) 4(2) 5(3) 6(8) 8(2) 9(1) 10(5)


Bird
Type: Bird (Crow?)
Effect: Instant Death
Movement Type: Angled (DownDownLeft, Down, DownDownRight, Chase Player)
Levels (no. in level): 1(2) 2(1) 3(1) 4(2) 6(5) 7(2) 10(4)


Blade

BladeR

Type: Blade
Effect: Instant Death
Movement Type: Horizontal (left to right), Chase player
Levels (no. in level): 11(1) 2(1) 4(2) 5(4) 6(2) 7(2) 10(1)


Coin2
Type: Coin (Type 1)
Effect: Instant Death
Movement Type: Down, Horizontal (left to right)
Levels (no. in level): 3(4) 6(5) 9(1)


Coin3
Type: Coin (Type 2)
Effect: Instant Death
Movement Type: Horizontal (left to right), Vertical (up & down)
Levels (no. in level): 3(2) 4(1) 5(3) 6(3) 8(2)


Dart
Type: Dart
Effect: Instant Death
Movement Type: Left, Right
Levels (no. in level): 1(1) 2(1) 3(2) 4(1) 5(2) 6(1) 8(4) 10(2)


DustCloud
Type: Dust Cloud
Effect: Instant Death
Movement Type: Horizontal (left to right), Vertical (up & down)
Levels (no. in level): 2(2) 5(1) 10(1)


Electricity

ElectricityR
Type: Electricity Generator
Effect: No Damage
Movement Type: Static
Levels (no. in level): 2(1)


Fireball
Type: Fireball
Effect: Launched form Volcanoes & Plant-Pots (Exploding) – Instant Death
Movement Type: Angled (random angle set a launch)
Levels (no. in level): 2(1) 5(1) 8(1)


Alien
Type: Gremlin
Effect: Instant Death
Movement Type: Static, Horizontal (left to right), Vertical (up & down) & angled (Down-left)
Levels (no. in level): 3(3) 4(3) 6(4) 7(5) 9(2) 10(3)


Mouth
Type: Mouth
Effect: Instant Death
Movement Type: Static, Horizontal (left to right)
Levels (no. in level): 6(1) 9(1) 10(1)


PlantPot

PlantPotR

Type: Robot (Moving)
Effect: Instant Death
Movement Type: Horizontal (left to right), Vertical (up & down), Down
Levels (no. in level): 2(3) 4(1) 6(1) 9(1) 10(1)


PlantPot

PlantPotR

Type: Robot (Exploding)
Effect: Instant Death
Movement Type: static, explodes launching fireballs once it has scrolled a third of the way down the screen
Levels (no. in level): 4(1) 6(1)


RotatingWheel
RotatingWheelR
Type: Spinning Wheel
Effect: Instant Death
Movement Type: Horizontal (left to right), Vertical (up & down), Down
Levels (no. in level): 3(3) 4(3) 5(2) 6(6) 9(3) 10(3)


SeaMissile1a

SeaMissile2
Type: Sea Missile
Effect: Instant Death
Movement Type: down
Levels (no. in level): 3(4) 4(2) 5(12) 6(16) 9(1) 10(6)


Volcano
Type: Volcano
Effect: Instant Death
Movement Type: static, explodes once it has scrolled a third of the way down the screen
Levels (no. in level): 2(1) 5(1) 8(1)


Bonuses

Basketball
Type: Basketball
Effect: 20 Jumps
Movement Type: Horizontal (left to right)
Levels (no. in level): 6(1)


Copyright
Type: Copyright
Effect: 10000 points
Movement Type: Horizontal (left to right)
Levels (no. in level): 6(1)


Environmental

BoxingGlove
Type: Boxing Glove
Effect: Appears once the player reaches the end of a (non-bonus) level – Player has 6 bounces to reach the exit – otherwise they are ‘punched’ towards the exit
Movement Type: Vertical (up & down)
Levels (no. in level): 1(1) 2(1) 3(1) 4(1) 5(1) 6(1) 7(1) 8(1) 9(1) 10(1)


Fan

FanR
Type: Fan
Effect: pushes player left-right (depending on direction fan is facing)
Movement Type: static
Levels (no. in level): 4(2) 5(2) 6(2) 8(2)


JumpGateA
JumpGateB
Type: Jump Gate
Effect: player collides with entrance gate disappears from the game until re-appearing from an exit gate
Movement Type: static
Levels (no. in level): 3(2) 4(2) 5(2) 6(7)


MovingTile
Type: MovingTile
Effect: player can bounce safely on moving tile
Movement Type: Horizontal (left to right)
Levels (no. in level): 3(2) 4(3) 5(3) 6(8) 7(2) 9(2) 10(3)


Movement Key

 Static  Horizontal  Vertical  Left  Right
Static Horizontal Vertical Left Right
 Down  DownLeft  DownDownLeft  DownDownRight  DownRight
Down Down Left DownDown Left DownDown Right Down Right
 Cross  Random  Angled  ChasePlayer  Explode
Cross Random Angled Chase Player Explode
 Fan  Mine
Fan Mine

Play the game (WebGL)


Next Post: 2.12 Bounder Without Bounder

Last post: 2.10 Enemies, Bonuses & Environmental C64

Contents page.


Adventures in Unity – 2.10 Enemies, Bonuses & Environmental C64

Official_unity_logo

Below is a (hopefully) complete list of NPC’s (i.e. everything that’s not a floor tile) used in the C64 version of Bounder.


Enemies

BearTrap

Type: Bear Trap
Effect: Instant Death
Movement Type: Static
Levels (no. in level): 4(7) 6(4) 7(6) 9(3)


BinocularsH
BinocularsHB
BinocularsVB
BinocularsVB
Type: Binoculars
Effect: Instant Death
Movement Type: Horizontal (left to right), Vertical (up & down), Cross
Levels (no. in level): 2(13) 4(16) 5(12) 6(25) 7(33) 8(23) 9(14) 10(17)


Blade
Type: Blade
Effect: Instant Death
Movement Type: Horizontal (left to right), rotating (fan), roaming (random movement)
Levels (no. in level): 1(3) 2(3) 3(3) 4(4) 5(1) 6(7) 7(2) 10(4)


BottleAxe
Type: Bottle Axe(?)
Effect: Instant Death
Movement Type: Right
Levels (no. in level): 3(4) 4(2) 7(4)


CoinB1
Type: Vampire Coin
Effect: Instant Death
Movement Type: Down
Levels (no. in level): 3(6) 5(11) 6(15) 8(18) 9(8) 10(3)


Dart
Type: Dart
Effect: Instant Death
Movement Type: Right
Levels (no. in level): 3(4) 4(8) 9(8)


Electricity
Type: Electricity
Effect: Player can collide with generators without dying, electricity stops momentarily to let players through
Movement Type: static
Levels (no. in level): 2(1) 5(4) 7(1) 8(1) 10(1)


Fan

FanB
Type: Fan
Effect: Pushes player left-right (depending on direction fan is facing)
Movement Type: Static
Levels (no. in level): 3(4) 7(5) 8(6) 10(2)


MineB2
Type: Mine
Effect: Explodes once it has scrolled a third of the way down the screen – launching four mini-mines which travel in diagonal direction until off screen
Movement Type: Static
Levels (no. in level): 1(1) 9(3)
MineA


PterodactylB2

Type: Pterodactyl
Effect: Instant Death
Movement Type: Angled (DownLeft, Down, DownRight)
Levels (no. in level): 1(1) 3(1) 5(1) 6(1) 8(1)


Spaceship
Type: Missile
Effect: Instant Death
Movement Type: Down – Launches above the player – Initial X position is set to the player x position
Levels (no. in level): 1(1) 2(6) 3(4) 4(4) 5(4) 6(14) 7(10) 8(9) 9(9) 10(14)


SeaMissile1C2
Type: Sea Missile
Effect: Instant Death
Movement Type: Down
Levels (no. in level): 3(2) 5(4) 6(12)


VolcanoB
Type: Volcano
Effect: launches Fireballs
Movement Type: Static
Levels (no. in level): 4(1) 5(1) 6(2) 7(1) 8(1) 9(1)


FireBall
Type: Fireball
Effect: Instant Death
Movement Type: Angled (random angle set a launch)
Levels (no. in level): 4(1) 5(1) 6(2) 7(1) 8(1) 9(1)


Bonuses

Basketball
Type: Basketball
Effect: 20 Jumps
Movement Type: Static
Levels (no. in level): 3(1) 4(2) 5(2) 6(1) 7(2) 8(2) 9(2) 10(2)


Bug
Type: Bug
Effect: 30000 points
Movement Type: Down
Levels (no. in level): 4(1) 6(2) 8(1) 9(2)


Copyright
Type: Copyright
Effect: 10000 points
Movement Type: Down
Levels (no. in level): 3(1) 4(1) 5(1) 6(1) 7(1)


Environmental

SpringBuffer
Type: Spring Buffer
Effect: Pushes player left/right (depending on direction fan is facing)
Movement Type: Static
Levels (no. in level): 3(3) 8(2)


JumpgateA
JumpgateB
Type: Jump Gate
Effect: Player collides with entrance gate disappears from the game until re-appearing from an exit gate
Movement Type: Static
Levels (no. in level): 3(2) 4(2) 5(2) 6(4) 7(2) 9(4) 10(2)


BoxingGlove
Type: Boxing Glove
Effect: Appears once the player reaches the end of a (non-bonus) level – Player has 6 bounces to reach the exit – otherwise they are ‘punched’ towards the exit
Movement Type: Vertical (up & down)
Levels (no. in level): all(1)


Movement Key

 Static  Horizontal  Vertical  Left  Right
Static Horizontal Vertical Left Right
 Down  DownLeft  DownDownLeft  DownDownRight  DownRight
Down Down Left DownDown Left DownDown Right Down Right
 Cross  Random  Angled  ChasePlayer  Explode
Cross Random Angled Chase Player Explode
 Fan  Mine
Fan Mine

Play the game (WebGL)


Next Post: 2.11 Enemies, Bonuses & Environmental Spectrum

Last post: 2.9 Tileset Spectrum

Contents page.