Forums » Code » Ingame database querying with ajax

Hello,

for my game I use an sql database in order to save everything and retrieve content.
For this purpose I wrote some ajax code that those of you who are also using a database system (instead of storing in cookies) can use.

Especially people using a server like one.com will know what I am talking about. The disadvantage of using a database is (I think) that you cannot use it as an iphone-app directly (this is a gamble, i'm not sure). If this is not a problem for you and you stay with browser games , the huge advantage is content-size. Data does not have to be embedded as arrays in your code, the database contains everything you need.

All this said, let's get to the code.

It consists of 2 parts. The first part is a plugin you will require in your ig.main. The second part is the php-file necessary for the database-lookup (also put it in plugins it does not have be in the ig.main require).

code for database lookup function:

ig.module( 
    'plugins.databaselookup' 
)
.requires(
    'impact.impact'
)
.defines(function(){
	
DatabaseLookup = ig.Class.extend({
	

    init: function(lookup_table, lookup_var,lookup_value,return_var ) {
    	
        this.lookup_table = lookup_table;
        this.lookup_var = lookup_var;
        this.lookup_value = lookup_value;
        this.return_var = return_var;
        this.out_text = "voor_invulling";
        
        var queryString = "?lookup_table=" + lookup_table + "&lookup_var=" + lookup_var + "&lookup_value=" + lookup_value + "&return_var=" + return_var;
        
    	this.ajaxRequest;  // The variable that makes Ajax possible!	
    	try{
    		// Opera 8.0+, Firefox, Safari
    		ajaxRequest = new XMLHttpRequest();
    	} catch (e){
    		// Internet Explorer Browsers
    		try{
    			ajaxRequest = new ActiveXObject("Msxml2.XMLHTTP");
    		} catch (e) {
    			try{
    				ajaxRequest = new ActiveXObject("Microsoft.XMLHTTP");
    			} catch (e){
    				// Something went wrong
    				alert("Your browser broke!");
    				return false;
    			}
    		}
    	}
  	  
    	ajaxRequest.onreadystatechange = function()
    	{  	
    		ig.sqlstate = ajaxRequest.readyState;
    		//alert(ajaxRequest.readyState)	;
    		if(ajaxRequest.readyState == 4)
    		{
    		  ig.out_text = ajaxRequest.responseText;		 
    		}	
    	}
    	 	
    	ajaxRequest.open("GET", "/impact/lib/plugins/ajax_query.php" + queryString, true);	
    	ajaxRequest.send(null); 
  	
    //END INIT
    }	

  });

});

adjustments to be made by you!:

  	ajaxRequest.open("GET", "/impact/lib/plugins/ajax_query.php" + queryString, true);

checks for the php file. You need to adapt the path according to it's location on your server (i myself put it in plugins like the databasequerying function itself)

php-file code:

<?php

//PART A CONNECT: ADAPT THIS TO YOUR DATABASE!!!

$dbhost = "yoursite.com.mysql";
$dbuser = "yourusername";
$dbpass = "yourpassword";
$dbname = "databasename";

  //Connect to MySQL Server
mysql_connect($dbhost, $dbuser, $dbpass);
  //Select Database
mysql_select_db($dbname) or die(mysql_error());
	
//PART B TRANSFERRED VARIABLES
$lookup_table = $_GET["lookup_table"];
$lookup_var = $_GET["lookup_var"];
$lookup_value = $_GET["lookup_value"];
$return_var = $_GET["return_var"];
 
  // Escape User Input to help prevent SQL Injection
$lookup_table = mysql_real_escape_string($lookup_table);
$lookup_var = mysql_real_escape_string($lookup_var);
$lookup_value = mysql_real_escape_string($lookup_value);
$return_var = mysql_real_escape_string($return_var);

//PART C QUERY
  //build query
$query = "SELECT * FROM $lookup_table WHERE $lookup_var = $lookup_value";
//Execute query
$qry_result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($qry_result);
//PART D FUNCTION OUTPUT
echo $row[$return_var];

?>

adjustments to be made by you!:

$dbhost = "yoursite.com.mysql";
$dbuser = "yourusername";
$dbpass = "yourpassword";
$dbname = "databasename";

these need to be filled out. If you have an online server you have gotten this in an email. For local WAMP servers the dbhost is often "localhost", the dbuser is often "root" , $dbpass is nothing and the $dbname depends on what your database name is (you will have filled this out somewhere).

actually now I'm posting this I remember I didn't adjust the php file to deal with the difference between numeric and character lookup variables. I will adjust that later and repost it when I changed it and there is enough interest in this technique.

For usage of the function you could for example put it with an entity and upon selecting the entity , the database will be queried.

      if( ig.input.pressed('context') ) {
               
                    this.civdatabase = new DatabaseLookup("usertable","id",71,"username");
                    
 this.wasselected = 1;
                }

The result will be stored in a global variable (I might change this later since I don't think it's a pretty solution) called ig.out_text.

However it is important to know that querying a database takes time and the code will generate a return variable in order for you to be able to take that into account. if ig.sqlstate ==4 the query will be done.

The following example uses the ig.out_text in a notification wordballoon, created by a genius I must say (I really like the wordballoon) , that can be found on this forum (though the code of the word balloon was slightly adapted in this version so don't just copy the wordballoon call)

        if (ig.sqlstate == 4 &&  this.wasselected == 1){
   
           	 ig.game.myNoteManager.spawnWordBalloon(this,0, new ig.Font('media/sleenee.font.png'),
             ig.out_text,this.pos.x, this.pos.y,
             {vel: { x: 0, y: 0 },  alpha: 1, lifetime: 2.2, fadetime: 0.3 });
           	      	           	        	
           	this.wasselected = 0;         	
           
        }

Ok, I will have probably forgotten a few things but I felt like posting this so if you have questions , just ask. Given the amount of help I already had on this forum I just felled like posting something significant in return.

Sleenee

good tutorial!

i use a similar system but using Google App Engine's datastore.

just curious, how many people here use ImpactJS + SQL?

I use local storage rather than sql - I have no need for collecting data as my game is more of a learning experience for me than a source of making money. The only thing I use local storage for is the savegame so people can start where they left off.

I plan on using MySQL with my games.

I don't have a game in a state where I can start using this code, but I will soon!

Thanks Sleenee!

I also plan on using SQL but with Ruby on Rails instead of PHP.

But at the moment I have to worry much more about Frontend-Stuff :)

yep, games are a lot about the front end and how you affect the player psychologically. Make them happy first, then make your servers happy.

Thanks for the guide. Will want to use PHP/MSQL backend to support persistent data. This will be handy for me to learn to do it. Am a impactjs newbie, still trying to figure out the frontend, will dive into localstorage 1st for a enjoyable, simple phone game :)