[Solved]Using MySQL in unity through c#

Hello there,
I've been trying to understand MySQL and php in order to setup a database for my game. So what I did was create a very basic php script to try and get some data from my database, which I access through Unity.

While this approach is functional I ran into a problem when I tried to get data from a specific database entry, depending on its ID. Because of that I tried to get rid of the php part and just access it all though c#.

So i'm trying to access a Players database, which holds players and their stats (simple INT fields in the database such as Strength, speed...). Once i'm connected to this DB i'd like to pass an ID and get the stats of the corresponding player back (for instance under the form of a string which i'll subdivide later on)

Here's what I've got so far :

Code (CSharp):

1. using System.Collections;
2. using System.Collections.Generic;
3. using UnityEngine;
4. using System;
5. using MySql.Data.MySqlClient;
6. using System.Data;
7.  
8. public class CsDataLoader : MonoBehaviour
9. {
10.  
11.     void Start()
12.     {
13.         string cs = @"server=localhost;userid=*****;
14.            password=*******;database=*****";
15.  
16.         MySqlConnection conn = null;
17.        
18.         try
19.         {
20.           conn = new MySqlConnection(cs);
21.           conn.Open();
22.  
23.           MySqlCommand cmd = new MySqlCommand();
24.           cmd.Connection = conn;
25.           cmd.CommandText = "SELECT ID, Nickname, Team, Strength, Speed, Balance, Hands, Toughness, Reach, Agility, Pass, Shoot, IQ FROM players";
26.           cmd.Prepare();
27.          
28.  
29.         } catch (MySqlException ex)
30.         {
31.           Console.WriteLine("Error: {0}",  ex.ToString());
32.  
33.        
34.         }
35.     }
36. }

However I'm stuck here... I don't really know how to proceed once i did the SELECT command. How do I use the corresponding values ?

Thanks in advance

 

I'm not entirely sure if I understand what you're asking, but I think this might be what you're looking for:
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.read(v=vs.110).aspx

 

Thanks for your quick answer
Sorry for being unclear i am still struggling with php and mysql
I am not sure how i would use the documentation you linked here, sorry..

so If i'm right the snippet I have written up there :
-takes login data
-makes a connection out of it
-from this connection, accesses the database fields and "SELECTS" the different fields (= stats : str, speed...) from the "players" database

But what I can't figure out how to do is how to complete this code because at this point; if I understood what I have written correctly, this line :

Code (CSharp):

1. cmd.CommandText = "SELECT ID, Nickname, Team, Strength, Speed, Balance, Hands, Toughness, Reach, Agility, Pass, Shoot, IQ FROM players";

"gets" every field from every player in the database.

So I still need to :
- complete this to fetch only ONE entry (player) in the db,, according to its ID (which I would pass from another scrpit)
-pass and print the values I got to Unity
But I can't find how to do that through c# ...

I hope this was understandable

Regards,
Aes

 

Using a login intermediary which actually accesses the database is usually the right way to do things. PHP isn't the fastest way , but it'll do if you haven't got a massive number of users yet. You don't want to expose the database to the Internet as there are more options for exploitation, and most hosts won't let you either (or it won't be the default setting). Accessing it through a REST API, gRPC or some other way you can secure is always the safest option.

The game server can have direct access to the database if they run on the same network, but you might still want to break it up into client, login frontend and game server.

The client contacts the login server, sends credentials and gets a token (a unique hash generated on the fly) and a game server address back. This login server might also be the service which starts the game server, or the game server might always be running.

The client connects to the game server and passes the token as its ticket to entry. The game server verifies with the database or login server that this token is valid. The game server only needs to look up the player attributes+IP address using the token as the search term ("select * from players where username=whatever limit 1" or similar).

If you decide to make the game server the login server you can skip a few steps and just connect the game client immediately (no token, get attributes right if the password hash matches). But then you don't have a separate service to take a load off the game server, and you won't have the opportunity for a queue system or sending players to an available game server elsewhere.

For password storage it's recommended to use some form of bcrypt, and to compare every byte of the password hash before returning success or failure. This makes all login attempts happen in the same time, rather than making failed attempts faster. Brute force hacks will be harder, in other words. With bcrypt you can scale the cost of calculating the password hash so that it takes a reasonable amount of time (50-100ms should be fine) on whichever server hardware you use.

And if you write it in PHP you should use PDO, not raw MySQL functions or anything like that. PDO gives you prepared statements to protect against SQL injection. It's also a common API for all database access, so you'll be ready to use it with any SQL database for other projects. Only the SQL dialect changes slightly.

Most of what you need to know is found in PHP/C# SQL tutorials on the web and have very little to do with Unity specifically, so there might be some translating to do to fit into your games. It might be best to work with JSON if you're passing data from a login server, since converting it to a C# structure is fairly straightforward.

 

Wow, thank you very much for the very detailed answer !
Actually your post made me realize how much I still lack knowledge about networking in general, and linking mysql databases to unity might just be too much for me at this point. I'll probably try to get at ease with php and mysql for other purposes before I try to implement that in unity !
But still, your post made the "architecture" of such a system a lot clearer to me so thanks for that, until I get a better understanding of php/mysql i'll toy around with databases in another way !



Since this post was about mysql i'll mark itclosed. However, I have one more subsidiary question :

You mentioned JSON at the end of your post. Actually, when I started toying around with databases, I read that I had basically 3 main choices for a database :
-the infamous php/MySql combo (which seems to be a bit overkill for me after your post)
-XML,
-JSON

Since I planned on using mysql at first I didnt really look any further into xml and json so far, but are they a relevant approach to setting up a very basic online database ? If so, is one of them more appropriate for use with unity ? And if none of these are, any other possible approach ? Otherwise, back to mysql

Thanks

 

More exactly, those are choices for communication, and some are really parts of the same chain

PHP/some DB (could be Postgres, if you have taste, My/Maria if you're boring, MSSQL if you're somehow not running on a cheap Linux VPS initially) is just the server side of things. XML or JSON would be two message formats. You could also be using a binary format, but JSON is about as compact as it gets when you remove whitespace

Not necessarily. But eventually you might do what you would do with those in some other language combination. You might even end up simply writing non-game servers (login, matchmaking, chat) in Unity. It's not too crazy, since there's plenty of C# code out there which works pretty well, especially with .NET 4.x supported now.

No, they're just data formats, as mentioned above. You'd contact the database with whatever the user is logging on to, and if data from that is necessary to communicate to the client, you could use JSON (preferably, as XML is a tad verbose).

If you've got a two-part setup with just a client and a game server, the latter being the login handler too, you don't even need to communicate that way. The servers gets all attributes into game objects, then they'll be replicated to the client as needed via RPC or whatever the networking API you use calls it (Unity has so many alternatives now).

It's probably best to start with a simpler system: Get a working game client which can run as the authoritative server which other clients connect to. No passwords, just LAN testing at first. Then build upon that as you learn. I suggest starting with authoritative, as you shouldn't get used to trusting the clients. Those guys always lie

 

note - if you know the ID you want ahead of time you can add to your SELECT statement a WHERE clause so it would be like

Code (csharp):

1. string sql = "SELECT * FROM `table` WHERE `table`.`ID` = 2"

and it will only return a single row - this will save you from iterating thru the dbreader and checking every ID field.

SELECT * is short for SELECT followed by every column name in the table so if you really want every column you don't need to change your query if you change your table later.

actually here is a snippet from my project which uses SQLite (pretty sure the SQL is going to be exactly the same as MySQL)

Code (CSharp):

1.  
2. string _name = "";
3. string _description = "";
4. int _offense = 0;
5. int _defense = 0;
6. int id = 1;
7. string table = "map_block_type";
8. dbconn.command.CommandText = string.Format("SELECT * FROM `{0}` WHERE `id` = {1} LIMIT 1;", table, id.ToString());
9. System.Data.IDataReader reader = dbconn.command.ExecuteReader();
10. while(reader.Read()){
11.     _name = reader["name"].ToString();
12.     _description = reader["description"].ToString();
13.     _offense = Convert.ToInt32(reader["offense"]);
14.     _defense = Convert.ToInt32(reader["defense"]);
15.     string[] textures = reader["textures"].ToString().Split('|');
16.     foreach(string t in textures){
17.         _textures.Add(t);
18.     }
19. }

"LIMIT 1" guarantees only 1 row is selected even though I don't really need it here if my ID field is unique

so one thing I am using here for convenience is the reader[COLUMN NAME] instead of reader[NUMBER] because I like checking by column name instead of remembering what order the column was in for my tables

another thing you notice is that I have to convert the values to the right type because they are all considered objects. I have also used an adhoc serialized array in the "textures" column in that example, "string.Split('|')" is the equivalent to PHP's "explode('|', $string)"

 

Thanks a ton, both of your posts proved very instructive and useful !
I actually managed to connect my game to my databases that I hosted on some free (pretty bad, but it'll do the trick for learning) hosting service, along with a few php files hosted there that I "trigger" from unity to retrieve some data (your snippet did help a lot @IsaacNewbton )

I think I understand the difference between all the elements @orb mentioned now. Still, I went for PHP/My/maria (I guess taste will come once I have a better understanding of it !). It's really dirty, probably begging for sql injections of all sorts ( unity client => php hosted script => hosted db=> hosted php script back to unity with data), but it's already far beyond what I expected posting here !
Since all the questions I have left might be more relevant on a dedicated php/mysql forum i'll close this topic, thanks again for your help
Regards,
Aes

NB. Oh, actually, one last question if you don't mind, @orb you mentioned the plethora of options for networking, i guess you were talking about utilities like Photon / UNet ? If so what is their role in the architecture you (very accurately) described to me?

 

As long as you use PDO access methods that should be fine. If not, stop and go read up on PDO

Well, there are several different systems for networking between Unity instances, and Photon is that and other services running externally. There's some overlap. This sticky has examples of just using what's available in Unity, communicating among Unity instances:
https://forum.unity.com/threads/collection-of-multiplayer-samples.355642/

Photon Bolt (previously not owned by the Photon folks) is a variation on the same sort of peer-to-peer networking, but with a tie-in to their services too. It's the next level if you want some parts managed. If you can't write a standalone matchmaker/chat service, this is where you find your happy place.