Legacy GM how to send and receive sql data

R

royer14

Guest
Hi, I have had this doubt for a long time and my goal is only to store, for example, game scores in a sql or mysql database. I have the following commands in sql.

Code:
1)   mysql.connector.connect(host="remotemysql.com", database="yvrcdG", user="yvdG",password="hdE7W",port=3306)

2)  "UPDATE `tabla` SET `name`=........................................."

3)   "INSERT INTO `tabla`(`name`, `Score`, `Comment`) VALUES ('Royer',5,'Interesting')"
many say that I should use http_get and set but I don't understand very well how I can use it in this situation.
 

FrostyCat

Redemption Seeker
You're getting advice from a bunch of amateurs at best if many of them think GET is appropriate for your situation. Requests that involve creating new resources should always be done over POST.

The idea here is to create a web endpoint that accepts your request via HTTPS or HTTP, then proxies the database calls for you. Here is an old post from me with an example for how to retrieve and submit data this way.
 

NightFrost

Member
You may also want to consider changing some of your SQL connection details as you just posted them to a public forum for all to see.
 
R

royer14

Guest
You may also want to consider changing some of your SQL connection details as you just posted them to a public forum for all to see.
excuse me, edit
1) mysql.connector.connect(host="remotemysql.com", database="yvRswCrcdG", user="yvRswCrcdG",password="hRoTaydE7W",port=3306)

2) "UPDATE `tabla1` SET `name`=........................................."

3) "INSERT INTO `tabla`(`name`, `Score`, `Comment`) VALUES ('Royer',5,'Interesting')"
 

FrostyCat

Redemption Seeker
Hey FrostyCat do you have another example of your old post ?
It appears the link is now dead.

thanks
Here are the examples originally from the dead link:
http_get() doesn't allow you to connect to a MySQL database. The most it can do is allowing you to request a server-side script (the PHP page, in this case) that connects to a MySQL database for you. Whether it reads or writes depends on the server-side script's contents.

Here is a basic PHP page that reads from a database with GET parameters:
PHP:
<?php
//CORS Header for HTML5 export
header("Access-Control-Allow-Origin: *");

try {
  //Connect
  $dbc = new PDO("mysql:host=127.0.0.1;dbname=database", "username", "password");
  $dbc->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
  //Prepare, bind and execute
  $stmt = $dbc->prepare("SELECT score, achieved_on FROM scores WHERE username = :username ORDER BY score DESC LIMIT 10");
  $stmt->setFetchMode(PDO::FETCH_ASSOC);
  $stmt->bindParam(':username', $_GET['username'], PDO::PARAM_STR);
  $stmt->execute();
 
  //Respond in JSON
  echo json_encode($stmt->fetchAll());
}
catch (Exception $e) {
  //Not done
  echo "ERROR";
}

//Disconnect
$dbc = null
This will initiate a request to the page for the user "Alice":
GML:
score_request = http_get("http://127.0.0.1/top_scores.php?username=Alice");
To see the result of the request, use the HTTP event:
GML:
if (async_load[? "id"] == score_request) {
  switch (async_load[? "status"]) {
    case 1: break;
    case 0:
      var score_list = "";
      var json_data = json_decode(async_load[? "result"]);
      var inner_list = json_data[? "default"];
      var siz = ds_list_size(inner_list);
      var i, entry;
      for (i=0; i<siz; i++) {
        entry = inner_list[| i];
        score_list += "#" + string(i+1) + ": " + string(entry[? "score"]) + " (" + entry[? "achieved_on"] + ")");
      }
      ds_map_destroy(json_data);
      show_message_async("Alice's top scores:" + score_list);
    break;
    default:
      show_message_async("Failed to retrieve scores");
    break;
  }
}
Here is a basic PHP page that writes to a database with POST parameters (GET is inappropriate for non-idempotent requests):
PHP:
<?php
//CORS Header for HTML5 export
header("Access-Control-Allow-Origin: *");

try {
  //Connect
  $dbc = new PDO("mysql:host=127.0.0.1;dbname=database", "username", "password");
  $dbc->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
  //Prepare, bind and execute
  $stmt = $dbc->prepare("INSERT INTO scores (username, score, achieved_on) VALUES (:username, :score, NOW())");
  $stmt->bindParam(':username', $_POST['username'], PDO::PARAM_STR);
  $stmt->bindParam(':score', $_POST['score'], PDO::PARAM_INT);
  $stmt->execute();
 
  //Done
  echo "OK";
}
catch (Exception $e) {
  //Not done
  echo "ERROR";
}

//Disconnect
$dbc = null
This will initiate a request to the page for the user "Bob Brown" with a score of 3755:
GML:
submission_request = http_post_string("http://127.0.0.1/submit_score.php", "username=Bob%20Brown&score=3755");
Again, to see the result of the request, use the HTTP event:
GML:
if (async_load[? "id"] == submission_request) {
  switch (async_load[? "status"]) {
    case 1: break;
    case 0:
      if (async_load[? "result"] == "OK") {
        show_message_async("Submission successful.");
      }
      else {
        show_message_async("Submission failed.");
      }
    break;
    default:
      show_message_async("Submission failed.");
    break;
  }
}
Nowadays I have migrated to Request Master for the GML side (which has a much faster syntax and built-in character escaping) and Laravel for the PHP side (also less boilerplate and has third-party extensions that generate CRUD endpoints for me), but the idea of doing the SQL only on the server side remains valid.
 
Last edited:
Top