First Android Game - Part 20 - Save data - MySQL Oct 08, 2023

This is part 20 of making my first Android Game. If you missed part19, you can find it here. There are weapons that the user can buy. If the user starts the game over and come back into the game, it would be great if what they bought are carried over. It can be frustrating for the user if all the weapons are gone. We will be exploring different ways to save data. The first one we will explore is MySQL. I will be following this Tutorial playlist by BoardToBits To create a database I will need to setup a local server. Therefore, I will download MAMP from this site. When I open MAMP, I will first go to MAMP > Preferences > Server and change the Document Root. This is where all the PHP files will be stored I'll need setup the database. Select the Open WebStartPage. A http://localhost/MAMP/ will open. localhost refers to the root that we set in Web Server Document Root. MAMP is the splash page that MAMP made. We will select the hyper link that says phpMyAdmin At the home page select user accounts. You will see a "root" user account. This is the default. If you decide to make your application for public to use, you should create your own user account with a new user name and password Now we need to create a database to connect. Select database, I will name my database unity-sql-learning. Select create Create a table for the database. Name it player and set number of columns to 5 There are 5 rows where we need to populate information about them. Each row we will need to provide information about them. Those are the name, type, length/values, default, coaliation, Null, index, A_I, and comments. The first information is a unique identifier called id. We want the id to differentiate the players. Type is int, give a length 10. We won't worry about default, coliation and attributes. NULL is false. The index will be PRIMARY. A_I is autoincrement, set that to true. Next one is username, type is VARCHAR which is a combination of letters and numbers. Max length is 16 character. Skip default(because we want someone to populate this field), coaliation, attribute, null. Index needs to be unique. Skip A_I. Next is password, but we won't store password as plain text for security reasons. We will use hash and salt. Set both to VARCHAR because a good password have different characters. Length is 100 and 50 respectively. Skip the rest. Lastly is the score - this is the game data we want to store. Type is INT. Length is 10. Default as As defined and set to 0 because by default the score is 0. Skip the rest. Make sure you save. I will be using sublime text to code my php. Sublime can be downloaded here. In Unity, we will create 4 scenes: Main Menu, Login, Register, Game. Main Menu will have 3 buttons: RegisterMenu, Login, Play Game and a text that tells us if the user is logged in. RegisterMenu has a button that says register and two input fields that lets user enter their username and password. Login Scene has the same two inputfields as Register but the button will say Log in. Game Scene will have a button the increments the score and a button that exits the game and go back to Main Menu scene. In the Main Menu Scene and RegisterMenu scene, attach a script called MainMenu.cs and Registeration.cs to the canvas respectively. The MainMenu.cs will have reference to all 3 buttons and the text. Create a function for each button so that the user presses Register, Login and PlayGame button it will load the RegisterMenu scene, Login Scene and Game scene. I will wrap this code in the namespace MySQLLearning so that it does not conflict with my tomato vs potato game. This is because these scripts are for testing. Attach a reference the OnClick on each button. For example, the Login has a reference to the canvas and the function is GoToLogin() In Registration.cs, I have a reference to the inputfields and the register button. The register button is not interactable at the start. It is also not interactable if the number of characters in username and password are less then 8 characters On the inputfields for username and password in RegisterMenu scene, attach a reference to VerifyInput to On Value Changed Create a public function called CallRegister() which will call a coroutine called RegisterRoutine(). On the Register button, have the OnClick reference to CallRegister() The RegisterRoutine() will make a post to the url specified. In this case it is http://localhost/register.php which is where our register.php script will be saved. In the form it will pass in the username and password that the user has entered in the inputfield. This will be passed into a WWWForm The routine will wait for the webrequest to finish making its request. If it fails due to Connection error, processing error or protical error. It will try again until its reaches the const int MAX_REQUEST_ATTEMPT_COUNT which I set to 2. Then it will print out the downloadhandler information If the request succeeds, I will check if the downloadhandler text does not contain a "0". This 0 will be from echo from the register.php. a non 0 will be an error code we add in a bit. If there is a 0, then everything passed and we load into the Login scene. Now we will go into sublime text to create register.php. I will be saving this php script in the folder specified in the document root. In any php scripts, we need to encapsulate them in . We will make a connection to our database. We will store that in a variable called $con. To connect to the database will make a function call mysqli_connect(). We will pass into the function the location of the database, which in this case is localhost. Then the user name which in this case is root, and also the password which is root. The password won't be seen in the browser and only stored on the backend so it is ok to hardcode the password here. Lastly we put in the database which in this case is unity-sql-learning. Now we want to check if the connection happened by doing an if statement to see if there were any errors mysqli_connect_errno(). If there is, we will echo 0. Echo is like debug log, it's the text the prints into the downloadhandler text. We will echo 1 if there is an error. We will exit() if there is an error. If it is successful, we will create a variable called username and password. Remember we get the username and password from the WWWForm. Using $_POST where $_ means it is a constant thing and we check for POST to see if there is the variable name and password from the form. Next we will double check if the name already exist, if the name exist we will not allow the user to add the name by using a query. First we create a variable called namecheckquery. We set the variable to "SELECT username FROM players WHERE username='" . $username . "';"; player is the table and username is the column. We concatenate a string by using periods . which is the php version of concatenating. Next, we create a variable namecheck which is set to a function call mysqli_query(). This function takes in our connection variable con our query variable namecheckquery. If this query did not work we say or die("2: Name check query failed") which will exit the code AND display information "2". Now we see if this name exists by checking if any rows were returned by doing an if statement and checking mysqli_num_rows($namecheck) > 0. If this is true then the name exists and we echo an error code. Otherwise, everything passed then we can add the user to the table. We need to make sure to salt and hash the password because we can't store passwords as plain text. Hashing means they can't immediately some information and get some password. Salting means we don't let hackers use lookup tables to break through encrypted passwords. So first create a salt variable. Assign it to \$5\$rounds=5000\$. Note the \ tells us write the actual symbol, in this case the $ symbol. The $ symbol is not any thing special. We are using a SHA256 to encrypt passwords (indicated by the 5), it will use 5000 shifts and come up with a nest of code to encrypt the password. We then concatenate an additional line, can be anything that we want. We want a 16 character salt, and in this case we use the username as part of the salt. We pass in the username and concatenate on last \$. The hash variable will be assigned to crypt($password, $salt) Then we will use the query that inserts the information into the database. "INSERT INTO players (username, hash, salt) VALUES ('" . $username . "', '" . $hash . "', '" . $salt . "');"; we will send this query by doing mysqli_query($con, $insertuserquery) or die("4: Insert player query failed");. Lastly we will echo 0! Remember 0 is how we check downloadhandler text in our Registeration.cs Now when a user successfully registers, the player table will have a row. Notice that the ID is 3, meaning this is the the 3rd person that registered because autoicrement the id. The salt and hash are encrypted. Score by default is 0 Now we will be logging into the database and access player's information and use it in our game. A script called Login will be added to the canvas in Login scene. In the script, we are going do simliar logic with the register button. The submit button is only interactable when the inputfield username and password have more then 8 characters When the login button is pressed, it will call the Login function which calls the LoginRoutine which will make a POST request, the form will pass in the name field and the password field. The post will go to a script called login.php. If request fails. Print to the console the error If there are noerrors,we will check the downlohandler text first array has the character 0. We know there must be something in downloadhandler because the request passed. If there is no "0" then there is an error. Otherwise assign the a static username and score from DBManager.csand load the main menu scene. Note that we are using split and the tab delimiter. We are parsing the string to an integer for our score which will be stored in the second array The DBManager checks our state. It can check if user is logged in by checking if username is null. This username was assigned in Login.cs. If user logs out we set username to null. In Login.php, we check for connection error, if there is no connection error. We use the same information from register.php to get the name and password because we have the same form structure. Now we check if the name exists. We check if mysqli_num_rows($namecheck) !=1 then the name doesn't exist, we will echo an error code 5. Next we check the password. In register.php we hashed and salt. However this time we need to get the hash and salt and compare to the password the user has logged in with to see if they match. We will get login info from query. Notice the $namecheckquery is retrieving the username, salt and hash. The $existinginfo is assigned to mysqli_fetch_assoc($namecheck) then we convert exisitinginfo to salt and hash. The $loginhash will be assigned to crype which takes in the password and the salt from our table. If the exisiting hash does not match the login hash meaning not matching what it is stored in the table. In this case we echo the error code 6 - incorrect password. Otherwise, user logined successfully then we can return "0". We will use \t to separate the information and then return score from $exisitinginfo. $exisitinginfo has score because of our $namecheckquery where it has score in the query In MainMenu.cs, we can check if the user is logged in by displaying the logged in user text and also enable/disable the buttons depending on if the users are logged in. Now we want to save user data in the game scene. Specifically we want to save the user's score. A Game.cs is created and attached to the canvas in the Game scene. We will have two text field, one for the player's name and one for the score. There will be a button to increment the score by one. This will be assigned and added to DBManager.score There will be an exit button. When the exit button is selected, it will send a POST request to savedata.php to save the score to the user we want to save to. The form will be storing username and score. Again if the request fails, we print to the console the error If the request succeeds, we check that the php did not echo 0. If it is not 0 then something went wrong. Otherwise logout and go to main menu scene. In savedata.php, we again first connect to our database and make sure there are not connection error. We create two variables and check for name and score because that's what the form from Unity was sending Our query will be checking if the username from player table matches the username being sent from the form. If the username does not exist, print out an error code. Otherwise if the username exist then we will use the Update query like this to store the score to the matching username "UPDATE players SET score = " . $newscore . " WHERE username = '" . $username . "';". This query is stored in the variableupdatequery. We then send it to our database where the variable con has our database info mysqli_query($con, $updatequery). We will also do a or die("7: Save query failed") if something failed. Below shows how register works in game and in the database Below shows how login and saving score works in game and in the database See part 21 here

Recent blogs See all blogs