Using MySQL with Unity

Even games must store data like top scores. In this article, Lance Talbert explains how to save data using MySQL with Unity.

SQL is the language of data management, and Unity is a game engine. The two seem to have very different goals, with one focusing on managing a database while the other is built primarily for the creation of video games. Can the two work together? As is so often the case in the world of programming, the answer is a resounding “yes.” As far as the games industry is concerned, databases are most useful in applications that require items like leader boards, stat tracking, and more. While a single-player focused adventure game may not need database integration, a massive racing game with thousands of players might.

With this in mind, a demonstration is in order. An app will be created that can receive the high scores stored within a MySQL database as well as put new data into it. Within the app itself, the top five players and their scores will be displayed. A variety of tools will be used to accomplish this. This demonstration uses WampServer and phpMyAdmin to create the database, PHP for server scripts, and of course, Unity and C# for app creation. If you’re more comfortable with other database management and creation tools, then those should work just as well for this project.

Database creation

Start by creating your database. Open your database management program of choice and create a new database, giving it the name highscores.

Creating a new database in WAMP using phpMyAdmin

Figure 1: Creating a new database in WAMP using phpMyAdmin

Next, create a table containing the data needed. Since the goal is just to display the player’s name and their score, you can leave the table at that. Go into the SQL tab and enter the following SQL command to create this table:

It’s recommended that you enter in some data for testing the application. While you could simply enter five new scores within the Unity app itself, it’ll be easier to test high score retrieval if there’s already some data within the database. You can use the below SQL command to quickly add some test data.

Now that the database is set up, you’ll need some PHP scripts to both retrieve and insert data into the database. These scripts will act something like the communicator between Unity and the MySQL database.

Server scripts – display.php

First, inside your server (this example’s file path is the WampServer folder, then www folder), create a folder named HighScoreGame for storing your PHP scripts. Then create a new PHP script called display and place it within this folder. Open the file in a code editor such as Notepad++ and enter the following code. Note that you may have to change the values of hostname, username, and password depending on your configuration.

This script is pretty straightforward. It’s just establishing a connection to the database before going through the scores table and retrieving the top five scores and their respective players. You’ll notice the echo statements at the end add in a newline and underscore. These might seem like strange additions to the results, but they will actually be used to help organize and display the results in a nice looking manner in the Unity app.

Server scripts – addscore.php

The next PHP script will be called addscore. As the name implies, this script will be responsible for adding data to the highscores database. All data that addscore tries to insert into the database will originate from within the Unity app. Like before, you may need to edit some of the variables to accommodate your specific setup. The important thing to note is the value of secretKey, which can be anything you like so long as the Unity app uses this value as well. This is because it will be used later on to create a secure hash which verifies that the scores being sent are indeed coming from the Unity app. If the Unity app uses a different value, then the script will not insert the input.

To keep things tidy, make sure this script is within the same folder as display. Once you have this code prepared, it’ll be time to open up Unity and begin creating the app that your users would use.

Unity App – User Interface

Now comes the part where the Unity app is made. This section is focused on making the user interface, which means using the editor to place buttons, input fields, and text. Start by creating a new project from the Unity Hub.

Creating a new project in Unity

Figure 2: Creating a new project in Unity

Any of the templates will work fine for this project, but the example here will use the basic 3D template. Give the project a name and set the location, then click the Create button.

Figure 3: Project name, template, and location

Figure 3: Project name, template, and location

This app’s user interface is a little involved, consisting of nine elements total. There are two buttons, one for retrieving the top five scores from a database and the other for sending a new score. Speaking of sending, there are two input fields asking for a name and score. There will also be a few text labels, with two of them containing the names and scores of the top five players. Start by creating the elements, and then they’ll be positioned in a way that looks appealing.

To create user interface (UI) elements, click the Create button in the Hierarchy window, indicated by the plus icon with a downward arrow next to it. From there, select UI, and then the UI element of choice. You will need five Text objects, two Input Field objects, and two Button objects.

Figure 3: Project name, template, and location

Figure 4: Creating UI objects

Next, you’ll position and resize the objects. Doing this requires editing properties in the Rect Transform component, located in the Inspector window while an object is selected. Changing the values is as simple as selecting the designated field and changing the value within it. Alternatively, you can use the Scene window to click and drag objects where you want them.

Setting the location, size, and name of an object

Figure 5: Setting the location, size, and name of an object

Below is a table listing all the objects and the position and size values of each object you’ve just created. They have also been assigned names to help you know which object is which. If you wish to match the names, then renaming the object is done by right clicking the object and choosing Rename. Additionally, what type of object each item is will be noted in this table.

Name

Object Type

Pos X

Pos Y

Width

Height

NameResults

Text

-365

50

162

297

ScoreResults

Text

-202

50

162

297

NameField

Input Field

245

58

160

30

ScoreField

Input Field

245

-36

160

30

NameFieldLabel

Text

108

58

103

30

ScoreFieldLabel

Text

108

-35

103

30

InputDirections

Text

246

148

274

51

SendScoreButton

Button

240

-130

170

65

RefreshHighScoreButton

Button

-287

-130

170

65

Table 1: All UI elements and their positions and sizes

Now all the objects need some proper placeholder text. This can be adjusted by selecting an object in the Hierarchy window, then navigating to the Text component in the Inspector window. For NameResults and ScoreResults, those can be left with empty text for now. You’ll need to edit their placeholder text for the input fields, which is found by clicking the arrow next to their name in the Hierarchy. This brings up all child objects found under the parent.

Revealing child objects

Figure 6: Revealing child objects

Find Placeholder, then edit the text to say “Enter Player’s Name.” Do the same with ScoreField, but the text should say “Enter Player’s Score” instead. Next, find the text objects being used to label the input fields, and change their text to reflect which field is being edited. For InputDirections, you’ll ask the player to “Enter a New Score!” To make it stand out a little more, adjust the font size property to thirty for InputDirections. Finally, the text for the buttons should be changed to better indicate what the buttons do. Starting with the SendScoreButton object, click the arrow next to it to show the child Text object. Enter the text “SEND SCORE” and fill in the button by adjusting the text size to twenty-five. As for the RefreshHighScoreButton, that button’s text can say “REFRESH HIGH SCORES” and be given a size of twenty. Once you’re finished, the user interface should look similar to the one seen in figure 7.

The finished UI

Figure 7: The finished UI

While not required, if you wish to change the alignment and size of any other text, all you need to do is find the Text component in each object and adjust the size, alignment, and other properties to your liking. After that, all that’s left before returning to coding is making the scripts themselves. You will need just a single C# scripts, called HighScoreControl. Creating scripts is done by right-clicking in the Assets window and navigating to Create->C# Script. This script will be called HighScoreControl.

Creating a new C# script

Figure 8: Creating a new C# script

Once you have created your script, open it by double clicking the script in the Assets window.

Unity App – HighScoreControl script

The HighScoreControl script is responsible for both sending and retrieving data to and from the MySQL database. Of these two actions, getting the scores is likely the easiest, as it involves creating a UniyWebRequest to send an HTTP request. That’s the point in which the display PHP script from earlier comes in. The server script will be executed and send data back into Unity to later display in the UI created earlier. Posting a new score follows a similar pattern, but requires you use the POST method to send data to addscore.php. Before doing this, the user’s input is also hashed using the SHA256 algorithm as a basic security measure. In real-world applications, you would likely need to do more than just hash your values to secure them. But, for the purpose of this example, this process will be kept simple. Once the values are sent through the HTTP request, addscore will handle the process of adding the score to the database.

For this script to function correctly, you’ll need the following using statements added at the top of your script:

Within the HighScoreControl class itself, add these variables:

You may recognize the secretyKey value from the addscore.php script. Make sure that this value matches the version seen in addscore exactly. Additionally, the two URL strings will be used to point Unity to where the server scripts are so data can be sent and retrieved from the database using those PHP scripts. Finally, four Text objects are set up in code. These will be used to change the text seen in the UI. Later on, Unity will be told which objects in the editor are to be changed using these variables.

Next come two methods, one for getting scores, the other for sending. These will be the methods tied to your buttons in the UI. Both methods start coroutines that will soon be defined, while quickly resetting some text to their defaults.

Speaking of coroutines, now would be a good time to go ahead and create the first one. The GetScores coroutine, as the name implies, will be responsible for communicating with the MySQL database in order to retrieve the top five scores along with player names currently stored in the database. As mentioned before, an HTTP request will be sent to communicate with the display.php script, which will then query the database. This PHP script will organize the data before Unity wetakes that data and separates the returned information to neatly fit it into the texts of nameResultText and scoreResultText. Those curious underscores found in the echo statements at the end of display will be used to more easily separate this data.

Next comes PostScores, which will handle the process of putting new data into the database. It takes two parameters which correspond to the inputs in the UI input fields. It will first hash the input along with the secret key, then construct a POST URL to send the player’s name and score with alongside the newly created hash. Like with GetScore, another HTTP request is sent, this time calling upon addscore.php to enter the new data into the database. The PHP script handles the rest from there, comparing the hash it received with the one it creates using similar data before inserting the data.

PostScores is still looking for the HashInput method at this point, so you’ll need to finish the script by adding this method. All it’s doing is taking a single input string, which is constructed using the player’s name, score, and your secret key, and constructing a hash based on that string using the SHA256 algorithm. It will then return this hash to PostScores to be sent in the HTTP request.

The HighScoreControl script is now complete. Be sure to save your work, then return to the Unity editor for a few final tasks.

Unity app – Finishing UI

Start by attaching the HighScoreControl script to the Main Camera object, as seen in figure 9.

Assigning HighScoreControl to Main Camera

Figure 9: Assigning HighScoreControl to Main Camera

Make sure Canvas is expanded to show all the UI elements underneath it, then drag the Text objects of both NameField and ScoreField into Name Text Input and Score Text Input, respectively. Then bring NameResults to Name Result Text, and ScoreResults to Score Result Text.

Setting all HighScoreControl fields

Figure 10: Setting all HighScoreControl fields

Next, choose the SendScoreButton and assign it the SendScoreBtn method. To do this, navigate to the Button component in the Inspector window and find the OnClick event list. There, click the plus icon to add a new event.

Creating a new OnClick event

Figure 11: Creating a new OnClick event

Drag the Main Camera object into the object field. Why the Main Camera object in particular? Because it’s the object with the HighScoreControl script attached to it.

Assigning an object to get the function from

Figure 12: Assigning an object to get the function from

Click on the No Function drop down menu, and then navigate to HighScoreControl->SendScoreBtn to set the method for this button.

Assigning the button function

Figure 13: Assigning the button function

Do the same for the RefreshHighScoreButton object, using the GetScoreBtn method instead. Once you’ve done that, try out the project using the play button at the top of the editor. Start by seeing if you can retrieve the current set of high scores. Then, try entering a score of your own, then refresh the top five to see the change.

The completed app using MySQL with Unity

Figure 14: The completed app

Using MySQL with Unity

A database can be easily made to store all kinds of data, whether it be interesting stats from various players or rankings. Now that you’ve been shown how to set up a basic database with a Unity app, you can expand this knowledge by experimenting with different database value types, multiple tables, and more. On the Unity side, you can try using data from a database to change a game in different ways. For instance, what if a player of higher rank meets one of a lower rank? What would you change within the game itself to reflect this scenario? While Unity is designed as a game engine, there’s also applications outside of video games that you can use it for that utilize these same database integration concepts. There’s much that can be done with this tech, some knowledge, and a little creativity.

If you liked this article, you might also like Saving Game Data with Unity.