[Advanced Tutorial] Saving Highscores with MySQL Databases

[Tr!KzZ]

Intro:
With this Tutorial I'm going to show an easy way to save and retrieve values, in this example a simple highscore file, to a database on the web. For this tutorial I'm going to use an offline database for test purposes. You can easily transfer that database to any webhost you like (for example ohost, or funpic).

Guide:

1. What do you need?
2. Setting up the (offline) DB
3. Creating the Unity Scripts
4. Creating the php Scripts
5. Final words

1 - What do you need?

• xampp (Simulates an offline database on your pc)
• Text Editor (You can use any editor you like, I prefer Notepad++)
• Unity (use any version you like to)

As said before, I'm using xampp within this tutorial so you can easily test the connection to a database. So it's not required to have an webhost with MySQL and a database.

You can dowload a windows version of XAMPP from this link: XAMPP Installer

For the installation, simply install it on the C:\ drive, that works the best.

Installation

After the installation, start the Control Panel and launch the Apache and MySQL Modules by clicking the "Start" button next to them:

Control Panel

You can either use the normal windows built-in Editor or download an Text-Editor of your choice, like Notepad++.
You can get the latest Unity Version here.


2 - Setting up the (offline) DB

With Xampp installed, simply open your browser (IE, Firefox, etc.) and type in this line: http://localhost/phpmyadmin (basic user is "root" without a password)

phpmyadmin

Now you can create your database. It can only have 64 characters and you shouldn't start with a number. The best would be to choose a fitting name, like gameX_database, this will hold all of your tables later on.

enter db name

If everything worked well you should get a green line that confirmes the creation.

confirmation

After you've created your database you can now select it under the "databases" tab. After you've selected it you're able to create new tables.
Hint: In most cases the database was automatically selected after creation.

You'll see this field:

create new table

in that you can simply enter your new table. For this example we choose "highscores" and create 3 fields.

First will be named "ID" and choose INT(10) (this means integer with a total of 10 characters), turn on auto-increment (this means it will count each value it gets, so first will be 1, then 2, 3, 4, etc.), not-null (means no empty values) and check the primary Key checkbox.

Second field will be names as "Name" with VARCHAR(15) and Not-Null.

Last one will be our "Score" and gets INT(10) with Not-Null.

Press OK at the bottom and your new table will be created, we access it later on to store our highscore values inside of it.


3 - Creating the Unity Scripts

Within Unity you can create a simple Level for testing purposes or use your already created scene. I won't explain how you can do that within this tutorial, there are others available for that.

Within the "Project" Tab right-click on an empty space and choose Create -> C# Script and name it "dbController.cs", this script will control our connection to the database. Attach this script to any GameObject you like, but it should be placed within the Scene from Start up, therefore attach it for example on the MainCamera Object.

If you haven't any variables for this, then declare them now:

[color=lightblue]public string[/color] name;
[color=lightblue]public int[/color] score;
[color=lightblue]public string[][/color] top10Scores;
[color=lightblue]public string[/color] db_url="http://localhost/unity_test/";      // this is the path to our xampp database folder

Next part is creating a new function that will start a connection with the given informations. So let's assume we already have played the Game and clicked a Button to save our current Score and Playername into the database (or at mission end, or whatever).

public void SaveScores()
{
	// this will start our function, Coroutine means that the game don't need to wait for the results and won't stop or interupt gameplay
	StartCoroutine(SaveScores());
}

And this is our Coroutine that will be started:

IEnumerator SaveScores()
{
	// first we create a new WWWForm, that means a "post" command goes out to our database (for futher information just google "post" and "get" commands for html/php
	WWWForm form = new WWWForm();

	// with this line we will give a new name and save our score into that name
	// those "" indicate a string and attach the score after the comma to it
	form.AddField("newName", name);
	form.AddField("newScore", score);

	// the next line will start our php file that saves the Score and attaches the saved values from the "form" to it
	// For this tutorial I've used a new variable "db_url" that stores the path
	WWW webRequest = new WWW(db_url + "SaveScore.php", form);

	// with this line we'll wait until we get an info back
	yield return webRequest;
}

This was the save function, that stores new values into the table of our database. But sometimes we want to retrieve that information, for example to show a Top10 statistic.
It's basically the same way to do this, but mostly you just want to send 1 variable, like a name or the ID to get the other values.
For a Top10 Info we don't have to sent any variable, because we can get and sort them via the MySQL queries.

IEnumerator LoadScores()
{
	// we don't need to store any variable in this, just run the php file
	WWW webRequest = new WWW(db_url + "LoadScore.php");

	// now we wait again for the feedback of the command
	yield return webRequest;

	// this is a GUIText that will display the scores in game.
	gameObject.guiText.text = webRequest.text;
}

with this load function we run our php file, get the info from our database and sent it back to Unity.
After the value is sent back our wait is over and runs the next part of the function, that will store the values from our sent info into our Unity array called top10Scores.
Don't forget to load the LoadScore function via the StartCoroutine() command.


4 - Creating the php Scripts

Now the last part. Simply create a new textfile and rename it to "SaveScore.php", next right-click on this file and open it with the Editor of your choice (for me Notepad++) and copy this code into it. It is commented within the code and starts with //, so no need for further description here.

SaveScore.php

<?php
	// create the connection to our database with following values: location of our databse
	// (with xampp it's "localhost"), next is the login ("name" and "password").
	// if the connection can not be established we get an error message, that we've entered after "or die"
	$sql_connect = mysql_connect("localhost", "root", "") or die ("no DB Connection");
	
	// after we're logged in, we can call our database
	mysql_select_db("gameX_database") or die ("DB not found");
	
	// now we store our sent information from Unity in php variables, we can work with
	$score = $_POST['newScore'];
	$name = $_POST['newName'];
	
	// Now we simply add/insert our values into our "highscores" table
	// we first choose the columns and then add our values
	// we don't need to fill in any value into the ID part, as it automatically gets a new value depending on the entries
	mysql_query("INSERT INTO highscores (Name, Score) VALUES ($name,$score);");

	// we're done now, so we can close the connection
	mysql_close($sql_connect);
?>

Now create another textfile and rename it to "LoadScore.php" and copy & paste the following lines to it. Same as above, comments are inside the script.

LoadScores.php

<?php
	// create the connection to our database with following values: location of our databse
	// (with xampp it's "localhost"), next is the login ("name" and "password").
	// if the connection can not be established we get an error message, that we've entered after "or die"
	$sql_connect = mysql_connect("localhost", "root", "") or die ("no DB Connection");
	
	// after we're logged in, we can call our database
	mysql_select_db("gameX_database") or die ("DB not found");
	
	// now we simply get the scores and sort them by their value. we also add a limit of 5, so we only
	// select the 5 highest values. The * means we search through every value.
	$query = "SELECT * FROM highscores ORDER by Score DESC LIMIT 5";
	
	// now we store our selected values into a result variable
    $result = mysql_query($query);
	
	// this will select the whole row we found the score at
    $num_results = mysql_num_rows($result);  
 
	// at the end we will get 5 rows with only the name and score values in each row
    for($i = 0; $i < $num_results; $i++)
    {
         $row = mysql_fetch_array($result);
		 // the echo command is used as the returned value for our Unity Script
         echo $row['Name'] . "\t" . $row['Score'] . "\n";
    }

	// we're done now, so we can close the connection
	mysql_close($sql_connect);
?>

save the files. Within the xampp path you'll find a folder called "htdocs". Create a new folder inside of it and call it "unity_test" and copy your php files into that folder.


5 - Final words

Well, this is my very first tutorial, and I'm not really good at it. Hope it could be helpful to someone ^^ I might change and edit parts later on, as I had not enough time to do all correct. Might change some images, description, etc. later on to make it more readable and easier to understand and learn from.

Any feedback, tipps, or questions are welcome.
Enjoy, and maybe you now have highscores in your game?

 

[Dr Super Good]

Why not use Microsoft SQL server?

 

[Tr!KzZ]

You can use any MySQL Servers you want to. I jus tused xampp as an example. It's basically good to test stuff offline without needing to upload stuff all the time or to use complicated urls. If the system works properly offline, then you can easily change some things for the online mode, like on funpic (a free webhost). When erros come up, you can be sure that this might be caused by some spelling mistakes or other small stuffs regarding the webhost or the connection to it, and not the written system as you already tested it offline.

You can use whatever software you like and which is able to do the things you want to.

 

[Statharas]

I just use MySQL. Get at me.