Search Unity

  1. Megacity Metro Demo now available. Download now.
    Dismiss Notice
  2. Unity support for visionOS is now available. Learn more in our blog post.
    Dismiss Notice

Setup mySQL, insert value to mySQL, retrieve data from mySQL

Discussion in 'Scripting' started by missy_pooh, Mar 8, 2012.

  1. missy_pooh

    missy_pooh

    Joined:
    Jun 1, 2011
    Posts:
    150
    Hello everyone, I understand that there are some documentation/tutorials/discussion around the forum talking about sql lite, mySQL+php. But somehow i feel that it is not really clear and somehow incomplete in some sense. Pardon me if i am wrong. So basically for this question that i am posting (i am confused and stucked on how to setup mySQL and used it), i would appreciate that everyone could help out and provide the solution/step by step/links so that people who have the intention to set up database(mySQL) will have clear ideas/view on how to start it.

    1. How to setup mySQL? Like we started off by connecting to the database (mySQL), after that we start to insert value to database?? if possible, provide code snippet.

    2. Validation part for login and store the values (Chinese words, how can i do it?).
    3. Retrieve data from database and display to the players.

    Actually i have already set up my database and it's working fine. Am using the sql lite, but there are some issues, webplayer doesn't communicate with the sql lite (maybe some security reasons)that wise i have to change to use mySQL with php/WWW form (after some research). What i am trying to do is to store player information like username, password,score,chinese word they formed and also need to retrieve data to display for monitoring purposes.

    Thank you so much for spending time to read it.

    P.S. I just want to mention that i have been searching around the forum/everywhere, bits and pieces from here and there on how to setup mySQL + php, retrieving data.. But somehow it confused me even more >< That wise i have decided to ask a question and hope that those who know how to do could answer it.I believe this will definitely help others. :)
     
  2. _zeta

    _zeta

    Joined:
    Dec 24, 2011
    Posts:
    99
  3. missy_pooh

    missy_pooh

    Joined:
    Jun 1, 2011
    Posts:
    150
    hi nagata, thank for the link.
    bump
     
  4. Tseng

    Tseng

    Joined:
    Nov 29, 2010
    Posts:
    1,217
    Just look for MySQL + PHP on google, because if you mySQL you must have a PHP (or some other server sided script language) to accept HTTP Requests with the data and read or save data to be mysql.

    Under NO CIRCUMSTANCES you should EVER have a direct Unity <-> MySQL connection. NEVER! First rule in Client server programming is: Never trust the Client. The Client can be manipulated, reverse engineered or the data stream can be manipulated/changed. It's not secure.

    Always do:
    a) Client sends request/data to the server.
    b) The server validates the data and adds it or throws an error

    In Unity you just need the WWW and WWWForm classes to communicate with your PHP script. Everything other interaction with the mysql must be drom from the server sided script
     
  5. Morning

    Morning

    Joined:
    Feb 4, 2012
    Posts:
    1,141
    UNLESS you want to write an editor extension to have direct access to mysql.
     
  6. andorov

    andorov

    Joined:
    Feb 10, 2011
    Posts:
    1,061
    Most databases now a days have very customizable security needs. In many databases, you can write entire procedures and call them remotely from a client. You can even handle per-user authentication within the scripts.

    You're right about never trusting the client, but you can program that functionality directly into the database.
     
  7. missy_pooh

    missy_pooh

    Joined:
    Jun 1, 2011
    Posts:
    150
    Yes. I understand the logic. We have to prevent cheating. It is not good to have client to be directly connect with mySQL.

    Well, as i mention in the post that i have already do relevant research before posting this questions to the unity people. I understand this logic : No one will want to answer/help people who didn't even to do research and just approach for help right? ><

    I know there are alot alot of the similar tutorials/discussion on this. As mentioned, i am confused how to get started on it. Pardon me, i am not trying to say that those aren't useful. So this is why i decided to post a question and will appreciate that those who have experienced with mySQL + php before to share their experience step by step with others. I strongly believe that there are people who also need this kind of step by step help. My "step by step" mean that beside those code snippet, what you will need to be careful of to prevent the errors kind. For example, you have to make that you have all the dlls inside Plugins file or you should place your php file in the script file etc. I know this sound like "spoonsfeeding" or whatever you can think of, but a small mistake(move) that you made, can give you alot of hard time.

    Thank you for those replying to the thread. I am still trying to figure out how can i do with it.
     
  8. RonHiler

    RonHiler

    Joined:
    Nov 3, 2011
    Posts:
    207
    Step one would be to set up a script on your server, then at the top, add these lines:

    using MySql.Data;
    using MySql.Data.MySqlClient;

    Unity will need access to the following two files (you may have to get these off the internet if you don't have them in your Unity instal folder anywhere)

    MySQL.Data.DLL
    System.Data.DLL

    I put those into my Assets/Plugins folder. Once you have that set up and working, I can help further.
     
  9. Tseng

    Tseng

    Joined:
    Nov 29, 2010
    Posts:
    1,217
    Well, what you are asking is outside of Unity Scope. This is a Unity3D Forum and hence the main things discussed here are scripting/game developing stuff. Other forums are more appropiate for different kind of things. It's also not a general game development forum (like gamedev.net etc.), it's specifically a Unity3D forum. And not just any unity3d forum but the official one ;)

    Depending on what kind of server you have, what OS you run or what (script-)languages you use, there are like 1000 ways to make a Unity game communicate with a server and the database behind it, so it's quite beyond the scope.

    From Unity3D side, there is not much you have to learn about the communication, as I said WWW and WWWForm classes are all you need, it should be enough for most needs. Of course you can use .NET/Mono Http classes if you want to directly communicate, but that needs bit more knowledge of the classes.

    There are also dozen of protocols you can use. You can make something yourself, you could use JSON to communicate, you could use XML, protobuf, binary serialization etc.

    JSON is quite popular, as it's easy human readable format, quite compact (smaller than XML) and there are enough Json library implementations on most of the systems.

    Protobuf is somewhat more complicated, but it's veryfast and has low bandwidth usage, though you may need a java or mono/.net server running (dunno if there is a PHP port of it). Same for binary serialization, as PHP doesn't really offer great tools for working with binary data.

    Just start small/with the basics, learn how to insert/select data from mysql. Then learn how GET/POST requests work in PHP. Then learn about security and how to validate the value or correctly coin MySQL queries to prevent SQL injection.
     
  10. missy_pooh

    missy_pooh

    Joined:
    Jun 1, 2011
    Posts:
    150
    Yes, i understand "Unity3D Forum and hence the main things discussed here are scripting/game developing stuff." But don't you feel that it is somehow indirectly link to unity scripting also, i am sorry if you don't feel it this way? It is also part of scripting too.

    Yes, from what i have found, i decide to use mySQL with php/WWW form. Yes, as what you have mention, my first move is to setup the sql connection first. Anyway to add on, i would need to store other languages( not english), how can i go about doing it?
     
  11. angrypenguin

    angrypenguin

    Joined:
    Dec 29, 2011
    Posts:
    15,614
    Not really, because other clients might try to connect. And if there's no middle-man ensuring that only valid operations can be run anything that gains access can do literally anything.
     
  12. Tseng

    Tseng

    Joined:
    Nov 29, 2010
    Posts:
    1,217
    But not Unity Scripting. As I said, the only thing you really need is the WWWForm class. It basically encapsulates a HttpRequest. You do the a request, send data to the server, and the server sends something back. Call it through WWW and the response from the server is stored in the "text". This can be simple text, a json string, an XML file, binary data, etc.

    It's up for you to decide which of these protocols best fits your skill/experience/needs. If you already worked with JSON serialization/deserialization, it may be a natural choice.

    I really don't your problem here. Just throw up "mysql php tutorial" and there are 1000s of results. Even the PHP Mysql documentation (http://php.net/mysql_, there is also some OOP version of mysql class if you prefer that) gives enough examples to be able to communicate with the database without any tutorial at all.

    You may want to try StackExchange (for specific programming related questions - but it's not the right place to ask for tutorials) or Data Bases Administrators (it's also part of Stackexchange, but specialized in database stuff).

    As for communication, you could give JSON a try. JSON serialization/deserialization in PHP is very easy, it's just a line of code

    PHP:
    $user = array();
    $user['id'] = 15;
    $user['name'] = "tom";
    $user['hobbies'] = array('programming''sports');

    $jsonResult json_encode($user);

    // result then contains: { "id" : 15, "name" : "tom", "hobbies" : ["programming, "sports"] } 

    // decoding is as easy as decoding: 

    $userData json_decode($jsonResulttrue);

    echo 
    "Username: ".$userData["username"];
    But as I said, PHP and DB tutorials goes quite beyond the scope, as they are done outside of unity.

    There are many ways to store multiple languages in your database, one being to create a table with at least 3 columns: id, lang, text. You make id+lang fields unique.

    Now if you have a string with id 1 you will insert the following data to the database

    HTML:
    id   |   lang    |    text
    1    |    en     |    Cancel
    1    |    de     |    Abbrechen
    
    And when you query, you use id + language as parameters and the SQL Query would like something like:
    
    SELECT text FROM stringstable WHERE id=1 AND lang='en';
    
     
  13. tiagoperes

    tiagoperes

    Joined:
    Feb 28, 2018
    Posts:
    4
    You can find a procedure in Unity Wiki with PHP, MySQL and C#/JavaScript.

    It consists of three steps
    1. Create a blank MySQL Database and a table.
    2. Create a PHP server side script (This will connect to the MySQL table, receive data from a Unity script (step 3), and query the database (the examples given are either inserting data or selecting)).
    3. Create the Unity Controller Script (This will connect to the PHP script created in step 2).
     
    Jobayerbiplob likes this.