With the advent of GM-HTML5, communicating with a MySQL database may prove an extremely useful resource for storing information. This is a simple guide to help you get started. For this example, we will be looking at a virtual basket of apples. Our database can hold a total of 16 apples - 8 green and 8 red.
If you're impatient or really don't care about how the process is done, check out the live example here.
Part 1 - Code
Step 0 - Basic Understanding of the Process
Like a lot of web development, this process will span multiple languages.
We will use GameMaker to execute a script from a JavaScript file. The JavaScript command loads a PHP file without reloading the page (AJAX). That PHP file has the ability to manipulate the MySQL database. After doing that, the PHP file can return data by displaying information needed to be returned. The JavaScript will accept that data and report it back to GameMaker.
This is not a guide for learning PHP, GML, JavaScript or SQL. It assumes that you have at least a basic understanding of all four languages. Without moderate PHP and SQL knowledge, manipulating the database may prove difficult. Refer to the PHP-MySQL guide at w3schools to learn more.
Step 1 - JavaScript
We will use JavaScript (AJAX) to execute a PHP file that contains the functions we wish to use. It returns the text that would be displayed on the PHP page if it was loaded by itself.
Parameters:
url - (String) Path to the PHP file with appended arguments.
async - (0 or 1) Run the command asynchronously (1) or synchronously (0).
function SQLCommand(url,async) {
if (window.XMLHttpRequest) {
xmlObject = new XMLHttpRequest();
} else {
xmlObject = new ActiveXObject("Microsoft.XMLHTTP");
}
xmlObject.open("GET",url,async);
xmlObject.send(null);
return xmlObject.responseText;
}For the most part, you won't have to edit this function.
Step 2 - PHP
PHP will be used to actually speak with the MySQL database and carry out the desired actions. If a value is to be returned, the PHP function will simply echo the result onto the page, which the JavaScript function above will read and report back to GameMaker.
We'll start with two standard functions that we'll use to connect and close the connection to the database. I've removed the login information in my example for obvious reasons.
<?
// database variables
$dbHost = "localhost";
$dbName = "<name here>";
$dbUser = "<user here>";
$dbPass = "<password here>";
// database connection functions
function dbConnect() {
global $dbCon, $dbHost, $dbName, $dbUser, $dbPass;
$dbCon = mysql_connect($dbHost,$dbUser,$dbPass);
return mysql_select_db($dbName);
}
function dbClose() {
global $dbCon, $dbHost, $dbName, $dbUser, $dbPass;
mysql_close($dbCon);
unset($dbCon);
}
?>
Now need a table in the database, which is our virtual apple basket.
Our basket of apples will be a table consisting of three fields:
1) appleid (INT) - a unique identifier for the apple
2) color (TINYTEXT) - the apple color (green or red)
3) weight (INT) - the weight, in grams, of the apple
We can use PHP or phpMyAdmin to create the table. The following PHP code will suffice.
<?
// connect to the database
dbConnect();
// create the table
mysql_query("CREATE TABLE apples
(
appleid int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(appleid),
color TINYTEXT,
weight INT
)
");
// close the connection to the database
dbClose();
?>
Now that we have a table to operate on and an easy way to connect to the database, we can define our custom functions. The PHP file accepts a standard argument, act, which decides which function to execute. Therefore, we have a switch statement that designates program control. Our example has three functions: Get apple lists, add an apple to a list and empty the entire basket.
<?
// custom SQL functions
switch($_GET['act']) {
case "getApples":
dbConnect();
$qry = mysql_query("SELECT * FROM apples WHERE color='$_GET[color]'");
$compile = "";
while ($data = mysql_fetch_array($qry)) {
if ($compile != "") $compile .= "|";
$compile .= $data['weight'];
}
dbClose();
echo $compile;
break;
case "addApple":
dbConnect();
mysql_query("INSERT INTO apples VALUES ('','$_GET[color]','$_GET[weight]')");
dbClose();
break;
case "removeApples":
dbConnect();
mysql_query("TRUNCATE TABLE apples");
dbClose();
break;
}
?>With the argument of "getApples", the PHP file will use the additional argument of "color" to return a list in the form "x|y|z|..." of the weights of all apples of that color. The "addApple" command will insert a new apple into the basket with the specified color. The "removeApples" command will simply truncate the table, emptying the basket completely. Notice how in order to return a value, we simply echo the information to display it on the page. Users never actually see this information - it is read by the JavaScript AJAX and returned to GameMaker.
Step 3 - GameMaker
Now that we have PHP to talk to the database and JavaScript to talk to the PHP, we need the GameMaker generated HTML5 to talk to the JavaScript. In a new GM-HTML5 project, we create a new extension that employs the use of the function we wrote in the beginning of the demonstration. Refer to this tutorial by JacksonYarr if you have difficulty with this step. Assign the name of the function SQLCommand with two arguments (string, double), and a string return type.
After we've imported the AJAX JavaScript function, we're essentially all set. Use the SQLCommand(url,async) function to load the PHP file with your desired parameters.
For our apple basket example, the logical first course of action is to create a script that updates the lists of green and red apples.
// updateApples();
// retrieve the green and red apples from the database
global.applesGreen = string_parse(SQLCommand("sql.php?act=getApples&color=green",0),"|",true);
global.applesRed = string_parse(SQLCommand("sql.php?act=getApples&color=red",0),"|",true);I'm using the string_parse script from GMLScripts.com, which I will provide the source to at the bottom of this post. The string_parse function takes the returned list of "x|y|z|..." and converts it into a ds_list containing the elements {x, y, z, ... }, much like the explode function of PHP.
Let's take a closer look at how GameMaker is getting the lists from the database. In particular, the list of green apples.
We call SQLCommand("sql.php?act=getApples&color=green",0) to get the raw list of green apples. You can see from the URL, the argument act is set to the function getApples, and the argument color is set to green. Refer back to our PHP function to see how these arguments direct program flow. We have the async parameter set to 0 in order to have the program wait for the SQL statement to go through before our program continues.
To add an apple to the basket, we simply use the addApples function. Remember, we don't want the basket to have more than 8 apples of each kind.
// add a green apple with a random weight into the database
if (ds_list_size(global.applesGreen) > 7) {
show_message("You can only have eight green apples at a time!");
} else {
randomWeight = random_range(100,250);
SQLCommand("sql.php?act=addApple&color=green&weight="+randomWeight,0);
}
// update the apple counts
updateApples();This code adds a green apple to the database, for reasons that I hope are now perfectly clear. Afterwards, we update the lists again to account for the new apple.
The procedure to empty the basket follows the same steps as the other SQL commands.
Part 2 - Example
View a live version of this example here. Note that multiple people may be accessing the example, so if you add one apple and two appear, it is because a different user added another.
It appears that exporting an editable project is not completely functional. When that is fixed, I will upload the editable example.
