Mysql Read/Write/Update PHP/C# script help.

Unity Engine
1

So as the title states I need help working with a script to access my databse, Here is what I have so far.

A running database with a login and register system. The user logs in with their user name and password or registers with their Name, Username, and Password.

Here is what I want to do now.

I want to add more columns to this line, A playerX, playerY, and playerZ. I can add these columns easily but here is the problem, I am VERY new to php. I am decently proficient in C# so the coding side of things looks good there.

So here is a rundown on what I need some help with.

Player Logs in and gets returned their Username.
Loads the game scene.
Send the username back to the database and check the XYZ affiliated with that name.
If there is no value, create one using a default number I set.
If it did find info in the respective columns, Return to me the numbers.
Finally I use some C# to get, store, and set those values respectively.

(oh also when the user quits the game those values are updated with new values)
Currently all this is handled with playerprefs, but I want it to check my database for security and convenience purposes.

How do I go about doing this? I just need to check and update the values, get a return then stick those values into variables. Can someone help me?

Thanks in advance!

2

this might help you to understand the basic implementation

http://wiki.unity3d.com/index.php?title=Server_Side_Highscores

3

Thanks, I will look over it and hopefully be able to adapt the code.

4 
<?php
        $db = mysql_connect('mysql_host', 'mysql_user', 'mysql_password') or die('Could not connect: ' . mysql_error());
        mysql_select_db('my_database') or die('Could not select database');

        // Strings must be escaped to prevent SQL injection attack.
        $name = mysql_real_escape_string($_GET['name'], $db);
        $PlayerX = mysql_real_escape_string($_GET['PlayerX'], $db);
        $PlayerY = mysql_real_escape_string($_GET['PlayerY'], $db);
        $PlayerZ = mysql_real_escape_string($_GET['PlayerZ'], $db);
        $hash = $_GET['hash'];

        $secretKey="mySecretKey"; # Change this value to match the value stored in the client javascript below

        $real_hash = md5($user . $PlayerX . $PlayerY . $PlayerZ . $secretKey);
        if($real_hash == $hash) {
            // Send variables for the MySQL database class.
            $query = "insert into Users values (NULL, '$user', '$PlayerX', '$PlayerY', '$PlayerZ');";
            $result = mysql_query($query) or die('Query failed: ' . mysql_error());
        }
?>

So here is what I have so far for adding the values, Could someone help look it over make sure im calling stuff correctly? I will add host, user, pass later when the code is correct.

So my table is called Users, it has (or will have these additional values) id, user, password, name, PlayerX, PlayerY, PlayerZ

Couple questions,

[1]What is the “Secret Key” being referenced in the script?
[2] does the “mysql_real_escape_string” have to be changed to “mysql_real_escape_float” considering that XYZ are floats, or do I do the converting client side?
[3] Considering the user will already exist due to the login/register that must happen first, should I just remove the user variables for the “insert into”?

Again thanks for the feedback, Im attempting to learn this…

5
  1. Secret Key is string for network security. It can be anything like “43565434” and should be same as what you have used on server side.
    2.mysql_real_escape_string uses to prevent SQL injection attack. $_GET[‘PlayerX’] will return the value of PlayerX.
    I think this will work fine.
$PlayerX = mysql_real_escape_string($_GET['PlayerX'], $db);
$PlayerY = mysql_real_escape_string($_GET['PlayerY'], $db);
$PlayerZ = mysql_real_escape_string($_GET['PlayerZ'], $db);

or you can combine position = “x=PlayerX,”+“y=PlayerY,”+“z=PlayerZ,”; and can do some parsing to get x,y,z on server side.
3. you can check user is already exit or not by SQL Query. If user exist then use SQL UPDATE to update existing user data.

6

First off thank you very much for your replies! They are very informational, I have just a few more questions if I may.

You said you can check to see if the user already exists, how would you do this? I know how to in C# but not sure on the PHP syntax. Would I simply just change “insert” to update? (again the user will already exist so this script doesnt need to create new users at all, just update existing ones.)

$query ="insert into Users values (NULL, '$user', '$PlayerX', '$PlayerY', '$PlayerZ');";

So Change it to…

$query ="update into Users values (NULL, '$user', '$PlayerX', '$PlayerY', '$PlayerZ');";

or

$query ="update Users values (NULL, '$user', '$PlayerX', '$PlayerY', '$PlayerZ');";

Another question,

<?php
    // Configuration
    $hostname = '';
    $username = '';
    $password = '';
    $database = '';

    try {
        $dbh = new PDO('mysql:host='. $hostname .';dbname='. $database, $username, $password);
    } catch(PDOException $e) {
        echo '<h1>An error has occurred.</h1><pre>', $e->getMessage() ,'</pre>';
    }

    $sth = $dbh->query('SELECT * FROM Users ORDER BY PlayerX DESC LIMIT 5');
    $sth->setFetchMode(PDO::FETCH_ASSOC);

    $result = $sth->fetchAll();

    if(count($result) > 0) {
        foreach($result as $r) {
            echo $r['user'], "\t", $r['PlayerX'], "\t", $r['PlayerY'], "\t", $r['PlayerZ'], "\n";
        }
    }
?>

I have this code which is working wonderfully, however how do I make it so it only displays the values from a specified user? So for example if I feed the url “testuser01” it brings all the values from “testuser01” and only the values from “testuser01” Currently it gives the values of XYZ for every registered user. Also how do I separate the return value? So I get an output of testuser01, X, Y,Z then i can take each individual piece and place them in variables

Again thank you for the help! It has been very eye opening.

7

SQL WHERE Syntax will help you to get “testuser01” values.

SELECT * FROM Users
WHERE Username='testuser01';
1 Like
8

By the way thanks! Your replies have got me a working script to add a player’s X, Y, and Z to a database, and has been implemented into my game already! I just need the second part of echo those values, receiving them client side then handling them from their.

For the second part, how would I do much the same thing? Would it just be something like:

 $user= mysql_real_escape_string($_GET['user'], $db);
SELECT * FROM Users WHERE Username='$user'
echo [PlayerX]

Would this allow me to access it giving it a username, selecting the PlayerX variable that has that username, Then printing it on the webpage? Or am I missing something here?

How would I accomplish this in the display function? I want it to work much like how addpos works, The user supplies their username and it echos back the X Y and Z

9

I got it working, Thank you again for all the help!

Here is the working code for displaying if anyone wants to reference it:

<?php
    // Send variables for the MySQL database class.
    $database = mysql_connect('your_host', 'your_user', 'your_pass') or die('Could not connect: ' . mysql_error());
    mysql_select_db('database_name') or die('Could not select database');

    $user = mysql_real_escape_string($_GET['user'], $database);

$query  = "SELECT user, PlayerX, PlayerY, PlayerZ  FROM Users WHERE user = '$user'";
$result = mysql_query($query) or die (mysql_error());

while($row = mysql_fetch_array($result))
{
    echo "{$row['PlayerX']} 
" . "{$row['PlayerY']} 
" . "{$row['PlayerZ']}";

    //echo "{$row['PlayerX']} 
" . "PlayerX: {$row['PlayerX']} 
" ;

}

        
?>
10

:slight_smile: