Unity MYSQL Connection (Account Creation and Login Verification)

Hello everyone, in this article I will explain how you can create a user login and a player account creation system for your game in Unity. Actually, you can control these quite easily with a simple method. The only thing you really need here is to have some knowledge of web design because in Unity, the MYSQL connection is not established directly with C# but via a website using the WWW structures in Unity.

If you don't know what WWW and WWWForm are, those who came here without directly researching the topic, I recommend you read this article first. After you learn about WWW and WWWForm, I think you will better understand what I am trying to explain here.

Understanding the Logic

You can use a program by rote, but such a thing is not really possible in software development, so I want to briefly talk about the logic of this work. Let’s grasp the logic of how to connect MySQL from Unity using WWW, and see how the system works.

First, we write a simple PHP script to send the user's data to the database. I coded 2 PHP pages for this project. The first is to create a user account and the second is to handle user login.

After creating the interface in Unity, I created a C# file called MySQLHelper and made it send the necessary data from the user to the required pages and display Debug output on the screen based on the responses from the site.

Setting Up the Database and Web Part

Since I do not have any web host, I install a web host software called MAMP on my computer and start Apache and MySQL from it.

You can use this link to download MAMP.

Creating a Database

To create a database, you need to visit "http://localhost/MAMP/index.php?page=phpmyadmin&language=English". (Only applicable for those who have downloaded MAMP.)

First, click the "New" section on the left and create a new database.

You can write the name you want to give to your database where it says test. I recommend not using Turkish characters. The "utf8-bin" option just to the right of where you provide the database name indicates that it accepts universal ascii codes, and you should select this data type so that users do not encounter problems creating accounts with Turkish characters in the future. Selecting "utf8-turkish-ci" will yield the same result.

After doing these, when you click the "Create" button, your database will be created.

If you have completed the steps so far correctly and did not encounter any problems, you will see a screen like the one above; here, we need to create a data table.

Since I will create a data table that holds user accounts, I enter "accounts" in the "Name" section and in the part next to it that says 4, indicating the number of data fields, I set it to 5 since I want to keep 5 records. Then, I click the "Go" button to continue.

The following screen will appear; here, we will define the names and data types of the data we will keep. Of course, we will also mark those that will be defined automatically as automatic data types.

Here, I will keep 5 data fields as "id", "kadi", "mail", "pass", "time", 2 of which will be defined automatically.

"id" and "time" will be defined automatically.

If you're wondering what these data fields do, let me answer.

id: It will be used as the user's account id and will indicate the order in which the account was created. It should be marked as "int" and you need to tick the "AI" part on the far right.

kadi: The field that acts as the username. It should be marked as "Text".

mail: The field that contains the user's email address. It should be marked as "Text".

pass: The field that contains the user's password. It should be marked as "Text".

time: The field that automatically stores the timestamp of when the user account was created. It should be marked as "TimeStamp" and you need to select "Current_TimeStamp" in the "Default" section.

Note: Do not name your database table randomly like I did, assign more organized and proper names...

If you entered the data correctly here, now click the "Save" button and create your data table.

If you encounter a screen like this and have not faced any issues, there should no longer be any problems with the database part and you do not need to take any new action.

Writing the Web Scripts

You will put the scripts you wrote into "C:\MAMP\htdocs\". Of course, you can place them in any folder inside htdocs. I put the web codes in "C:\MAMP\htdocs\UnityMySQL". Of course, the location where you put the file actually changes the URL you will access.

config.php is the PHP script file where I store the necessary user credentials to connect to the MySQL database, and for MAMP, it looks like this:

<?PHP
    $host = "localhost"; // MySQL Host
    $host_username = "root"; // MySQL username
    $host_password = "root"; // MySQL password
    $host_database = "deneme"; // MySQL database
?>

girisyap.php connects to the MySQL database, queries the incoming user data, and if matched, sends a user login response.

<?PHP
    include "config.php"; // calling config.php

    if(isset($_POST["kullaniciAdi"], $_POST["sifre"])){
        try{ // Start error controller
            $kullaniciAdi = $_POST["kullaniciAdi"];
            $sifre = $_POST["sifre"];

            $baglanti = new PDO("mysql:host=".$host.";dbname=".$host_database."" , $host_username, $host_password);
            $baglanti -> exec("SET NAMES utf8"); 
            $baglanti -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            $sorgu = $baglanti -> query("SELECT * FROM hesaplar WHERE kadi = '$kullaniciAdi' && pass = '$sifre'", PDO::FETCH_ASSOC);
            if($sorgu->rowCount()){
                echo "1";
            }else{
                echo "0";
            }           
        }catch(PDOException $e){ 
            die($e->getMessage()); 
        }
    }

?>

hesapolustur.php adds the user data coming from Unity to the database.

<?PHP
    include "config.php";

    if(isset($_POST["kullaniciAdi"], $_POST["mail"], $_POST["sifre"])){
        try{
            $kullaniciAdi = $_POST["kullaniciAdi"];
            $mail = $_POST["mail"];
            $sifre = $_POST["sifre"];

            $baglanti = new PDO("mysql:host=".$host.";dbname=".$host_database."" , $host_username, $host_password);
            $baglanti -> exec("SET NAMES utf8"); 
            $baglanti -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            $sorgu = $baglanti -> prepare("INSERT INTO hesaplar(kadi, mail, pass) VALUES(?,?,?)");
            $sorgu -> bindParam(1, $kullaniciAdi, PDO::PARAM_STR);
            $sorgu -> bindParam(2, $mail, PDO::PARAM_STR);
            $sorgu -> bindParam(3, $sifre, PDO::PARAM_STR);

            $sorgu -> execute();
            echo "1";
        }catch(PDOException $e){
            die($e->getMessage());
        }
    }

Note: Guys, I wrote this directly here without any security measures, such as whether an account has already been created with the same information, or encrypting the user's password with md5 or SHA methods. If you are going to release a game and want your users to create accounts, you should pay attention to such security measures!

Writing the Unity Codes

MySQLHelper

With MySQL Helper, we handle sending and receiving data.

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.Networking;
using UnityEngine.SceneManagement; // Library used for scene switching

public class MYSQLHelper:MonoBehaviour
{
    [SerializeField] // Makes the variable accessible from the Inspector window
    private string hesapOlusturURL = ""; // Variable for the account creation URL
    [SerializeField]
    private string girisYapURL = ""; // Variable for the login URL

    // Two variables called HesapOlustur and GirisYap accessible from anywhere, calling the main functions used inside StartCoroutine
    public void HesapOlustur(string kullaniciAdi, string ePosta, string sifre){
        StartCoroutine(_HesapOlustur(kullaniciAdi, ePosta, sifre));
    }
    public void GirisYap(string kullaniciAdi, string sifre, int acilacakSayfa){
        StartCoroutine(_GirisYap(kullaniciAdi, sifre , acilacakSayfa));
    }

    // Function to reload the current scene if login is successful,
    public void GirisYapildi(int sahneID){
        PlayerPrefs.SetInt("giris", 1); // Using PlayerPrefs to keep whether the user is logged in, thus not always showing the login screen.
        // PlayerPrefs = 1 means logged in.
        SceneManager.LoadScene(sahneID, LoadSceneMode.Single); // Reloading the scene
    }
    IEnumerator _HesapOlustur(string kullaniciAdi, string ePosta, string sifre)
    {
        yield return new WaitForEndOfFrame(); // Waits for the end of the last frame
        WWWForm hesapOlusturmaForm = new WWWForm(); // Creating a WWW form
        hesapOlusturmaForm.AddField("kullaniciAdi", kullaniciAdi); // Adding username to the form
        hesapOlusturmaForm.AddField("mail", ePosta); // Adding email to the form
        hesapOlusturmaForm.AddField("sifre", sifre); // Adding password to the form
 
        WWW veriGonder = new WWW(hesapOlusturURL, hesapOlusturmaForm); // Connects to the site via WWW to send data
        yield return veriGonder; // Sending the data
        if(veriGonder.text == "1"){ // If received 1 from the site
            Debug.Log("Account creation successful"); // Shows successful login
        }else{
            Debug.Log("An error occurred while creating the account! \nThe problem is not you, it's me. :')"); // If response is not 1, shows an error message
        }
    }

    IEnumerator _GirisYap(string kullaniciAdi, string sifre, int acilacakSayfa){
        yield return new WaitForEndOfFrame();
        WWWForm girisYapForm = new WWWForm();
        girisYapForm.AddField("kullaniciAdi", kullaniciAdi);
        girisYapForm.AddField("sifre", sifre);

        WWW veriGonder = new WWW(girisYapURL, girisYapForm);
        yield return veriGonder;
        if(veriGonder.text == "1"){
            Debug.Log("Login Successful");
            GirisYapildi(acilacakSayfa);
        }else if(veriGonder.text == "0"){
            Debug.Log("Username or password is incorrect!");
        }else {
            Debug.Log("An error occurred during login! \nThe problem is not you, it's me :')");
        }
    }
}

UIManager

With UIManager, we open and close panels and send the inputs from Inputs to the MYSQLHelper script.

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.UI;


public class UIManager : MonoBehaviour
{
    public GameObject[] paneller; // Assigns main screen, login and account creation panels here so you can toggle between them.
    public MYSQLHelper _mysqlHelper; // Drag the MySQL Helper Script here and access its functions from this.
    public InputField _hesapOlusturKullaniciAdi; // Account creation panel username input
    public InputField _hesapOlusturSifre; // Account creation panel password input
    public InputField _hesapOlusturEPosta; // Account creation panel email input
    public InputField _girisYapKullaniciAdi; // Login panel username input
    public InputField _girisYapSifre; // Login panel password input
    
    // Panel order
    // 0 MainScreen
    // 1 Login Panel
    // 2 Account Creation Panel

    void Awake(){
        if(PlayerPrefs.GetInt("giris") == 1){ // Check if the user is already logged in
            GirisEkraniKapat(); // If so, hide the login and account creation panels
        }
    }

    // Function to close login and account creation panels
    public void GirisEkraniKapat(){
        paneller[0].SetActive(false); // hides main panel
        paneller[1].SetActive(false); // hides login panel
        paneller[2].SetActive(false); // hides account creation panel

    }

    // Function called when login button is clicked
    public void GirisYap(){
        _mysqlHelper.GirisYap(_girisYapKullaniciAdi.text, _girisYapSifre.text, 0); // Calls login function in MYSQLHelper and sends required info
    }

    // Function called when create account button is clicked
    public void HesapOlustur(){
        _mysqlHelper.HesapOlustur(_hesapOlusturKullaniciAdi.text, _hesapOlusturEPosta.text, _hesapOlusturSifre.text); // Calls account creation function in MYSQLHelper script
    }
    
    // Opens or closes the login panel
    public void GirisPaneliAcKapat()
    {
        if (paneller[0].activeSelf)
        {
            paneller[0].SetActive(false);
            paneller[2].SetActive(false);
            paneller[1].SetActive(true);
        }
        else if (paneller[0].activeSelf == false)
        {
            paneller[0].SetActive(true);
            paneller[1].SetActive(false);
            paneller[2].SetActive(false);

        }
    }
    // Opens or closes the register panel
    public void KayitPaneliAcKapat()
    {
        if (paneller[0].activeSelf)
        {
            paneller[0].SetActive(false);
            paneller[1].SetActive(false);
            paneller[2].SetActive(true);
        }
        else if (paneller[0].activeSelf == false)
        {
            paneller[0].SetActive(true);
            paneller[1].SetActive(false);
            paneller[2].SetActive(false);

        }
    }
}

Unity Scene Section

Add one "Canvas" to your scene and place 3 panels inside.

On the first panel, add 2 buttons and label them "Login", "Create Account". With these, you will open the login and account creation panels.

On the second panel, add 2 inputs and 1 button. The first input will be for username, the second for password, and the button will be for logging in.

On the third panel, add 3 inputs and 1 button. The first input for username, the second for password, and the third for email address. The button will be for creating the account.

Also, add 2 empty objects to the scene. Name the first one AccountManager and attach the MYSQLHelper Script, and name the second one UIManager and attach the UIManager script.

After completing the steps above correctly, make sure your objects are properly placed on UIManager and AccountManager.

Note: As I always say, don't take me as a model, label your objects more properly...

Don't forget to assign the functions to your buttons (you need to assign a total of 4 buttons, I only showed 2 visually..)

If you have done everything up to here properly, now you can try creating an account...

Let's create an account and then check it in the database.

As you can see, our account has been added to the database. Now let's login to our account.

Yes, as you can see friends, we successfully created the account and logged into the account we created..

In the video, I do "ClearPrefabs" at the beginning because while preparing the article I had created an account and logged in for screenshots, so I do this to log out of the account.

If you want to add a logout button in the game, just write a function like "PlayerPrefs.SetInt(\"giris\", 0);" and call this function from the button.

Of course, do not forget to reload the scene afterwards.

I am ending this article here, take care of yourselves. See you in the next articles.

Note: If you are stuck anywhere you can ask in the comments below.

UrhobA

Unity MYSQL Connection (Account Creation and Login Verification)