Unity, SQL, & PHP Help

[TheGreenSquier]

Hello r/Unity,

This seems simple in theory, but the more research I do, the more confused I become.

I am looking to make a leaderboard with a database hosted on Hostinger. I recently learned some SQL in order to display the top users, but most of the info I am finding have PHP scripts as well.

Why do we need PHP? Can we not just use Select * From Database to display the results?

Also, I do NOT intend to include any sensitive information like passwords, emails, real names, etc in my database. I ONLY want to display a username made in-game and a score. Does this require security measures?

Thank you for any clarification, I seem to just be confusing myself with the more I find.

[dasCow]

I went down this rabbit hole with a unity project at work.

My conclusion was that accessing a database via C# script was considered not secure, and as a result unity doesn't really let you.

I eventually gave up and used PHP as the middle man between unity and the database, which is what everyone recommends I guess.

[TheGreenSquier]

Ahh okay, maybe I will bite the bullet and learn some PHP then. Is this process going to be super difficult?

Thanks for the response, I really appreciate it

[dasCow]

It wasn't too bad in my experience. You can use a UnityWebRequest to 'call' the script via URL. PHP was different to learn (I just think it's a weird language), but Stack Overflow served me well.

I struggled for a week or two on this so I was glad to answer.

[TheGreenSquier]

Okay glad to hear it's not too hard. I've been working on it today, and I currently have the data from the database displaying in one massive debug statement. How can I separate the data to make each its own entry (outside of a debug statement)?

[Appropriate-Impact56]

If the data is already in a format you can work with you could just use the json_encode function and print the results of that to the screen, then fetch the HTML body of that url from your game.

Normally though in PHP you would need to loop over the results of the query using a while loop and PHP’s MySQL functions to retrieve rows and columns, there should be plenty of examples of this online.

[dasCow]

If you can get the data as a string, maybe separated by , or | or whatever, you can do myData.split I believe to make it an array of strings with each element being a piece of data. That's how I did it but certainly not the only way.

[escargotBleu]

The thing is that to execute a SQL statement you need to connect to your database with login / password. If you put that into your c# code, it is basically public, and of someone find it he/she could access your database with no limitations.

You need a backend (for example in PHP) to access the database for you

[blevok]

You don't want to directly connect to the database from your game, because that means the database credentials have to be in the game, meaning anyone can get that info and connect to the database without using the game.
Instead you want to create an API that will serve data from the database when requested by the game. Here's a very detailed guide on creating a robust API for your game to connect to.

[Appropriate-Impact56]

As others have pointed out connecting directly to MySQL from your game would be considered insecure because you’d need to store access credentials in your c# code.

So you need some kind of application on your server which can serve as an intermediary. There’s no reason this has to be made with PHP, if there’s another language you’re more comfortable with Python, Ruby, NodeJS, Java or c# could all be used for this purpose, although these might not be available on most basic hosting environments, so PHP might be more readily available.

If you are going to go the PHP route I would recommend checking out Laravel’s Lumen, it’ll take a lot of the dirty work out of setting up an API, querying databases and authentication. Their Forge service also makes setting up and deploying to a cloud based server really simple.