Send and Receive Data to my Website database

:information_source: Attention Topic was automatically imported from the old Question2Answer platform.
:bust_in_silhouette: Asked By aadyainfotainment

Can someone help step by step on how to get and post data to my website database?

I am a complete nube on this. I want to achieve the following:

  1. Collect data from my game.
  2. Post it to a mysql database on my hosting server.
  3. When the game loads, get the data from my hosting server database.
  4. Display it in the game.

If possible, please help me with both server and client side steps and code.

:bust_in_silhouette: Reply From: wombatstampede

Any data and queries can be set up or retrieved in the form of dictionaries (combined with arrays). For easy transport via HTTP, those dictionaries can be converted from/to JSON.

Such a request could be a dictionary with some “action” field containing the basic action to perform. The second field could be a “params” which contains a dictionary with required query params (user name or similar). For updating data you could also add a “rows” field which is an array of dictionaries containing one or multiple data records.

The server return will be another JSON-> dictionary which could contain some field “result” (containing “OK” or an Error message) and also a “rows” variable.

How to do HTTP in Godot is roughly explained here:

You’ll want to do a POST for transmitting data. Which will also return data back from the server.

The server can be any HTTP server (i.e. apache) software. Using i.e. a PHP-script it is very easy to listen to http requests, decode the JSON data, query a MySQL/MariaDB Database and send the answer back in JSON. There are tons of examples in the web. Here’s one: How to convert MYSQL data to JSON using PHP

For PHP:
Essentially, you create a textfile like “gamedata.php” containing the php code and put it in the htdocs/web directory of your website. Optionally in a subfolder. The request address would look roughly like this “mywebsite.com is available for purchase - Sedo.com”.

Ok, that was about sending/getting data. Now about security:
This depends on how important that data is. For low security it might be sufficient to simply pass a password in the request parameters and check it in the PHP script. For more security there are nearly endless possitbilities like encrypted data or challenge/reponse logins with session ids.

Also:
Never directly send mysql statements via http or assemble sql statements directly of the received parameters using string functions. Use bound parameters or clean up any data field at least (i.e. only allowing "a-zA-Z0-9 "). The problem here is SQL-injection: SQL injection - Wikipedia
Limit the possible actions. I.e. limit the user rights for the user which the PHP script uses for the connect.

  • Avoid the deletion of records.
  • Backup the database often.

Also:
Tell your game users what you are doing. This will depend on the platform you are publishing on but most require some GDRP/Data Privacy statement. And mobile apps usually require some permission to be enabled to access the internet.

As for security, it should also be mentioned that HTTP requests should strive to always use HTTPS, especially for authentication purposes. You can get free SSL certificates at Let’s Encrypt.

Calinou | 2020-03-24 09:13

I did all my luck for posting data to the mysql database on webserver but unable to complete the complete the request.

My GDScript is as per below:

var myurl = (my php file web address)
var dataText = "testData"
var score = 10
var dict = {name: dataText,score: 10 }



# Called when the node enters the scene tree for the first time.
func _ready():
	pass # Replace with function body.


# Called every frame. 'delta' is the elapsed time since the previous frame.
#func _process(delta):
#	pass


func _on_Button_pressed():
	_make_post_request(myurl, dict, false)
	print("data sent")
	
func _make_post_request(url, data_to_send, use_ssl):
	# Convert data to json string:
	var query = JSON.print(data_to_send)
	# Add 'Content-Type' header:
	var headers = ["Content-Type: application/json"]
	$HTTPRequest.request(url, headers, use_ssl, HTTPClient.METHOD_POST, query)

My server side PHP code as per below:

<html>
    <head><meta http-equiv="Content-Type":"application/json"><head>

<?php

include 'DatabaseConfig.php' ;

$json = file_get_contents('php://input');

$_POST = json_decode($json);
 
//echo $_POST;  
 
 
 $con = mysqli_connect($HostName,$HostUser,$HostPass,$DatabaseName);
 
 $name = $_POST['name'];
 $score = $_POST['score']
 

 $Sql_Query = "insert into game_test (Name, score) values ('$name', '$score')";
 
 if(mysqli_query($con,$Sql_Query)){
 
 echo 'Data Submit Successfully';
 
 }
 else{
 
 echo 'Try Again';
 
 }
 mysqli_close($con);
?>
</html>

Can someone tell me what am I doing wrong?

aadyainfotainment | 2020-03-25 15:21

Doesn’t look too bad.
You may leave away the html and head tags and replace them by php code (the closing head tag is missing a slash anyway).

Here’s an example php to just echo your parameters:

<?php
header('Content-Type: text/plain');

$json = file_get_contents('php://input');
$data = json_decode($json, true);

echo "PHP: name:".$data["name"]." score: ".$data["score"]."\n";

?>

(I chose text/plain because, like in your script the php script only outputs text here)

Then you should check the output of the request in your godot code to see if and what the script returns. Sometimes it might also run on an error and you might have to check the web server error log.

You’re directly addressing $HTTPRequest in your code. So I guess there exists a child node “HTTPRequest”, right?

You could add that to ´_ready()`

$HTTPRequest.connect("request_completed", self, "_http_request_completed")

And then add a simple output handler:

func _http_request_completed(result, response_code, headers, body):
	print("rc: "+str(response_code)+", result: "+str(result),", body: "+body.get_string_from_utf8())

wombatstampede | 2020-03-25 16:41

@wombatstampede

You are a life saver. You have made life a lot easier for me and now I will be able to achieve endless results.

Thanks a ton again.

aadyainfotainment | 2020-03-25 17:27

Sorry to disturb again.

I am retrieving data from My web server with the following code

func _http_request_completed(result, response_code, headers, body):
	
	resultdict = body.get_string_from_ascii()
	print(resultdict)

The result is shown as per below:

Array
(
    [0] => Array
        (
            [Reg_id] => 1
            [Name] => Prashant R Vaingankar 
            [score] => 1700000
        )

    [1] => Array
        (
            [Reg_id] => 2
            [Name] => zyc
            [score] => 6778
        )

    [2] => Array
        (
            [Reg_id] => 3
            [Name] => Aadya
            [score] => 250000000
        )

    [3] => Array
        (
            [Reg_id] => 4
            [Name] => Testing
            [score] => 0
        )

)

My PHP Code is :

<?php
    header('Content-Type: application/json');
    include 'DatabaseConfig.php' ;
    
    
    $con = mysqli_connect($HostName,$HostUser,$HostPass,$DatabaseName);
     
     
    $Sql_Query = "select * from game_test";
     
    if(mysqli_query($con,$Sql_Query)){
     
    $result = mysqli_query($con,$Sql_Query);
     
    $json_array = array();
     
    while($row = mysqli_fetch_assoc($result))
    {
        $json_array[] = $row;
    }
     
    print_r($json_array);

    }
    else{
     
    echo("Error description: Query not sent" );
        
    }
    mysqli_close($con);
?>

1 - Now is it possible to get the data in Godot without printing it in PHP
2 - How do I convert the json to Godot readable dict.

When I try below:

var dict = parse_json(resultdict)
print(dict )

I get the error “Invalid get index ‘get_string_from_ascii’ (on base: ‘PoolByteArray’). Did you mean ‘.get_string_from_ascii()’ or funcref(obj, “get_string_from_ascii”) ?”

aadyainfotainment | 2020-03-26 12:18

use

echo json_encode($jsonarray);

instead of print_r() in your php code.

wombatstampede | 2020-03-26 13:41