Megabite #4 - Holding Onto Those Variables (With MySQL and a Simple Web Host)

UPDATE:  Thanks to some amazing Reddit users, the code here has been updated for extra security.  

Connecting your Unity web application to html or PHP files is actually quite easily done.  This could be useful for a great many reasons including: score lists, login/password, progress saving, etc.  In this Megabite article, I will be showing how to get everything situated for a “top 10” type of score list.  An example is now displayed whenever your ship is destroyed in GIRType.  Try a single playthrough of the game to see a working example.

For the purpose of this article, we will have to assume you have a web server to host your game, and the ability to create MySQL databases and tables.  While you may not need a great deal of experience with PHP/MySQL, any experience at all might be helpful.

Before any scripting, you will need to know a couple of things:

  • Your MySQL server address (in some cases this may be different than “localhost”)
  • Your database name and login/password information (the creation process will vary between hosts)

The basic process consists of 2 PHP files for our purpose.  We need one file to simply add information to the database, and another that easily lists the information in a way that Unity can easily read and display.  To easily create the table itself, however, I will also throw in this additional PHP file that can be pasted, saved/edited, and executed in order to set up your table within the database.  This only needs to be run once if you don’t see en error displayed.

 

Save this file as whatever you like and add a .php extension.  It will need to be edited to include the information you would like to use.  Once the correct sections are filled out, upload it to your server and navigate to it through a web browser.  If you see that your table was created, you can feel free to delete the file completely.

In the next section, we will use GET variables to add scores into the system.  Follow along to page #2.

PHP GET variables are typically created through HTML forms.  While it is possible to construct form data through Unity and send it as POST, I figured starting with GET might be easier for testing and debugging purposes.  If I get enough feedback on POST interest, we can cover that soon as well.

For those who may not have web-programming knowledge, a GET variable is the type of thing you see in your browser address bar that looks like this:

http://somewebsite.com/pagename.php?id=7489&color=yellow&time=tea

I color-coded the string so that it can be easily broken down.  Basically, we are going to somewebsite.com and looking at the page called pagename.php.  The question mark is a separating symbol that begins the extraction process for variables.  When you take a close look at this string, you can see there are 3 variables as an example: id, color, and time.  The & signs simply separate them, but our web browser and web server realize that these things aren't actually a part of the file we are trying to read.

So, this is all we need to be able to read and understand the basics of to work the script.  For our purpose, we are going to make sure we pull the variables "name" and "score" out of the web address and attribute them directly into our table.  While it isn't a life and death situation that should raise much concern for meddling, I will also point out that the end-user won't actually see these things in the web bar, and they won't just be able to insert information of their own unless they know the exact address of where the page was placed.

The PHP file to add the scores to our new table looks something like this:

 

Again, we only have to worry about the sections that are color-coded above.  Be sure to use the exact information you used in creating the table originally.  Once saved and uploaded, navigate to it and see what happens:

There has been an error grabbing the GET variables.

This is by design, and will occur in the event of something being malformed in the url.  In this case, unless you added in the name or score variables, you will see this error.  To test that the script is working you can simply make your browser go to something like this:

http://yourwebsite.com/folder/yourpage.php?name=hello&score=123

If you see the word "Added" on your screen, you will know that the name and score have now been inserted into your brand-new table.

Now, we just have to get our Unity game to send the right string to the browser at whatever time we like, and we can input data into our MySQL table.  Follow along to the next page and I'll show you how easily this can be done.

Fortunately, the developers who created Unity put a great deal of effort into adding smooth functions that can perform a good amount for you.  In this instance, we are simply using the WWW function to "navigate" to a web page, in essence.  Of course we aren't actually pulling up the web page to view it, but we just need Unity to send the data.

Because scoring systems will vary, we are just going to assume that you can access a variable called playerScore for the actual script.  If there is any question as to how I have the playerName variable set up in GIRType, here is the code snippet that I used.

At the beginning:

static var playerName : String = "Anonymous";

In the OnGUI section:

	GUI.Box (Rect (0,0,200,160), "Enter Your Name:");
	playerName = GUI.TextField(Rect(5,20,190,25), playerName, 25);

Another important section that keeps data around between levels is this:

function Awake () {
    DontDestroyOnLoad (transform.gameObject);
}

We enter our name, press the "go" button, and the next level loads.  Our name sticks around until the game is closed, and we have everything needed for a basic scoring system.  Getting back to that, here is the code that is used to actually access our new PHP page and database our score:

var wwwResponse = new WWW("http://website.com/folder/page.php?name=" + Data.playerName + "&score=" + Data.playerScore);
yield wwwResponse;

I have this encased within a function called AddScore() that is called if ever the ship explodes in GIRType.  With one life to live, the game is over and the score can be sent.  Your own game may differ, so just use it accordingly.  You can see from the example however, the script is completely simple to make use of.

With all of the variables you could save into a database this way, you can see just how easily a massive amount of data might be accessible with just slightly more complex scripting.  For those folks with mediocre to expert levels of PHP experience, much more is possible and within reach.

Before we move into the next section, a quick note: Unity has a security feature that will keep external web data from being sent while testing. It will say "SecurityException: No valid crossdomain policy available to allow access". I was perplexed in the beginning, but a simple Google search taught me much and more about how to get around it if I needed.  This won't even be an issue if your MySQL and Web Host are the same computer (as is the case with many hosts), so build and run your program to test it out before attempting anything extra that may not be needed.  In the "worst-case scenario", a simple XML file needs to be included with your game that allows it to communicate with any website you choose.

The last section will be the most exciting: displaying your score list within the game.  On the next page, I'll show the basic PHP and unity scripts to access and display the data however you like.

Before getting back into the PHP script, you'll need a way to display the data in your game.  While I chose to use a GUI Label within the existing post-crash box, you can do things however you like!  First, you'll want to declare the variable to use.  It will change once the scores are loaded, so a slower web server may display this longer.

var scoreDisplay = "Scores Loading...";

Again I chose to wrap my score loader within a function, seen here:

function DoScores() {
scoreText = WWW(highscoreUrl);
yield scoreText;
scoreDisplay = scoreText.text;
}

Your highscoreUrl variable is simply the name of the PHP file we will create next, so be creative, but remember the name and location of the page you choose.  Also, don't forget to declare it:

highscoreUrl = "http://mysite.com/folder/displayscores.php";

Lastly, you'll want to display the scoreDisplay variable in a manner of your choosing.  As a string, you can place it in a complicated GUI interface or you can simply add it to a box or label.  However simple or complex you want to go is up to you.  Before we can test it, however, we need that final piece of the puzzle: the simple PHP script that will spit out the contents of our table for Unity to read.

Open whichever PHP editor you like (I recommend Notepad++ or Komodo Edit for PC or Mac, respectively.) and paste in the display script

 

If you were a good student that followed along with Megabite #3 (Smooth Operators), you may recognize the FOR usage and immediately understand what it does.  If not, simply know that this script pulls the 10 highest scores and makes a list.  If you were to go there in a web browser with a non-empty table, you'd see something like this:

Frank 638105 Josh 511059 Callum 191945 Callum 102065 Frank 94417 Josh 40814 Callum 39274 Callum 38383 Callum 31715 Callum 26711

We don't have to make it pretty - we just have to make it readable.  For our simple purposes, this will do.  A line return is added into the section between each set of name/score as well as to display like a proper list.  The way all of these things work together in the current version of GIRType (v1.8) is to display this list as a GUI.Label upon death, like so:

Every game is different, so you may experience unique issues.  If you have trouble, feel free to contact me via the site contact form or the comments below and I will do my best to help.  (Scripting examples are likely needed to see what is going on).  I don't claim to be a master, but I should at least be able to get things started for you :).

Until next week!