An Example of Using LoadVars with PHP and a MySQL Database

An Example of Using LoadVars with a MySQL Database

Flash - Database Communications

There are three mechanisms for setting up communications between Flash and a database

Use LoadVars instances to send and receive data from serverside scripts (PHP, eg) that retrieve and save data to the database. Data may be sent via GET or POST, and is returned with var=value formatting.
Use the XML load method to call a serverside script which returns XML-formatted data. Data is sent via querystring appended to the script name (ie, a GET), and must be returned as correctly formatted XML.
Use Flash remoting, in which data objects are sent to and received from serverside scripts (which may include CFC's if Cold Fusion is used). Data is transmitted in a binary format, which is more efficient than either of the above (which transmit ascii data). Remoting (eg, as built into ColdFusion 6 and above on a Windows-based server, or something like AMFPHP on a Linux-based server) must be installed on the server for this method to work.

On this page, we'll look at using the first method to display and write high scores data to a table in a MySQL database. The same example using Flash Remoting with AMFPHP may be viewed here.

Define the Structure and Create a Table

In this example, our database will have one table with a primary key auto-increment id field (so each record has a way to be uniquely identified for editing or deletion), and fields for nickname, dateposted, and score. We'll make the firstname and lastname fields varchar 50, so they can be any number of letters up to 50. Dateposted will be saved as a string in the form yyyy-mm-dd, and score will be numeric. We'll make it of type smallint, and make sure our game is designed to have scores that cannot exceed 65,000. Here's the .sql to create such a table:

CREATE TABLE highscores ( id mediumint(9) NOT NULL auto_increment, nickname varchar(50) NOT NULL default '', dateposted varchar(10) NOT NULL default '', score mediumint(9) NOT NULL default '0', PRIMARY KEY (id) ) ENGINE=MyISAM;

If you have PHPMYADMIN (a program which allows modification of MySQL databases via the browser) installed on your server, you can click the SQL tab, paste in that code, and hit Go to create the table. Here is some dummy data you can use to populate it:

INSERT INTO highscores VALUES (1, 'Spiderman', '2005-09-07', 580); INSERT INTO highscores VALUES (2, 'Black Widow', '2005-02-01', 730); INSERT INTO highscores VALUES (3, 'Hulk', '2005-09-10', 150); INSERT INTO highscores VALUES (4, 'Underdog', '2005-03-05', 330);

Retrieving the database content and displaying it in a Datagrid

To get data out of the database into Flash, call a serverside script that runs a SQL SELECT statement and returns the data to Flash. In addition, your serverside script can also return an errorcode variable to tell Flash whether an error occurred (and what type it was). Here's a PHP script that returns an errorcode of 0 if the select was carried out without a problem, as well as a list of variable=value pairs with the retrieved data. The script returns a 1, 2 or 3 if any connection or query errors occurred, along with a description of the error (msg) if it was a query error:

<?
/*
   getscores.php: Retrieves score data from highscores table and returns 
                  data and status to Flash
   
   errorcode:
      0: successful select
      1: can't connect to server
      2: can't connect to database
      3: can't run query
*/

//  fill this in with the right data for your server/database config
$server = "";
$username = "";
$password = "";
$database = "";

//  mysql_connect: Open a connection to a MySQL Server
//  Returns a MySQL link identifier on success, or FALSE on failure.
if (!mysql_connect($server, $username, $password)) {
   $r_string = '&errorcode=1&';	
	
//  mysql_select_db: Sets the current active database on the server that's associated 
//    with the specified link identifier. Every subsequent call to mysql_query() 
//    will be made on the active database.
//  Returns TRUE on success or FALSE on failure.
} elseif (!mysql_select_db($database)) {
   $r_string = '&errorcode=2&';
	
//  mysql_query: Sends a query (to the currently active database
//  For SELECT, SHOW, DESCRIBE or EXPLAIN statements, mysql_query() returns a 
//     resource on success, or FALSE on error.  
//  For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_query() 
//     returns TRUE on success or FALSE on error. 
} else {
   $qr = mysql_query("SELECT * from highscores");
   if (!qr || mysql_num_rows($qr)==0) {
      $r_string = '&errorcode=3&msg='.mysql_error().'&';
   } else {
      $r_string = '&errorcode=0&n='.mysql_num_rows ($qr);
      $i = 0;
      while ($row = mysql_fetch_assoc ($qr)) {
         while (list ($key, $val) = each ($row)) {
            $r_string .= '&' . $key . $i . '=' . stripslashes($val);
         }
         $i++;
      }
      // add extra & to prevent returning extra chars at the end
      $r_string .='&';
   }
}
echo $r_string;
?>

In the Flash movie, we'll drag a datagrid component onto the stage from the Components panel, drag it out to 460 x 140 and name it scores_dg. This code in frame 1 will test the script above and make sure it dumps the data we expect to the browser window. Change the filepath to one that corresponds to the correct path from the swf to the PHP scripts on your server:

var select_lv:LoadVars = new LoadVars();
// create a string to show where to access the PHP script
var filepath:String;

// set the path to the PHP script based on whether this is being tested
// locally or online.  If local, use script saved on online webserver.
// Can use http://localhost instead if script is on local webserver.

if (_url.indexOf("http") != 0) filepath = "http://yourdomain.com/scripts/";
else filepath = "../scripts/";

select_lv.send(filepath + "getscores.php", "newwin", "GET");

If that's working, you should see something that starts like this in the browser window:

&errorcode=0&n=4&id0=1&nickname0=Spiderman&dateposted0=2005-09-07& (etc)

(This is the same thing you would see if you type the url straight into the browser window). Once we know that the PHP script is working correctly, we can change the Flash script to return the data to Flash and display it in the datagrid component. To do that, we change the send to a sendAndLoad, changing the second parameter to the name of the loadVars instance, as is required by the sendAndLoad method.

We'll create an array scoreInfo to be filled in with data returned from getscores.php, and an array errorMsgs to hold possible errorcodes returned by the script. In the select_lv onLoad function, we check for any errors returned and display an appropriate message for the user. If there were no errors, scoreInfo is filled with the returned data. This array is then used as the dataProvider for the datagrid. We'll also specify which fields should be displayed in the datagrid, their respective widths, and whether an automatic sort should be done when the column header is clicked or whether a custom sort will be applied:

select_lv.onLoad = function(ok:Boolean) {
   if (ok) {
      if (this.errorcode=="0") {
         for (var i:Number=0; i < this.n; i++) {
            scoreInfo.push(
               {record:this["id"+i],
                nickname:this["nickname"+i],
                score:Number(this["score"+i]),
                dateposted:this["dateposted"+i]
               });
         }
         // only display Nickname, Score, and Date Posted (not record id)
         scores_dg.columnNames = ["nickname", "score", "dateposted"];
         // set formatting of nickname column
         scores_dg.getColumnAt(0).width = 200;
         // trap header click event to sort case-insensitive on this field
         scores_dg.getColumnAt(0).sortOnHeaderRelease = false;
         // this property will keep track of whether sort is ascending or descending
         scores_dg.getColumnAt(0).sortedUp = false;
         scores_dg.getColumnAt(0).headerText = "Nickname";
		 
         // set formatting of score column
         scores_dg.getColumnAt(1).width = 100;
         // trap header click event to sort numerically
         scores_dg.getColumnAt(1).sortOnHeaderRelease = false;
         scores_dg.getColumnAt(1).sortedUp = false;
         scores_dg.getColumnAt(1).headerText = "Score";
		 
         // set formatting of date column
         // auto-sort will work fine for this column
         scores_dg.getColumnAt(2).width = 160;
         scores_dg.getColumnAt(2).headerText = "Date Posted";
			
         // set dataProvider for datagrid
         scores_dg.dataProvider = scoreInfo;
		
         // execute headerRelease function for correct sort when user clicks a header
         scores_dg.addEventListener("headerRelease", headerListener);
		 
         msg_ta.text = "Enter data and click Add to add a score.  Click a row and Delete ";
         msg_ta.text += "Selected to delete a score.  First four entries may not be deleted.";
      } else {
         // show kind of error
         msg_ta.text = errorMsgs[Number(this.errorcode)];
         // if query error, show mysql_error
         if (this.errorcode == "3") msg_ta.text += ": " + this.msg;
      }
   } else {
      // if loadvars failed (eg, if script not found)
      msg_ta.text = "Flash-database select operation failed";
   }
}
msg_ta.text = "Getting high scores from database...";
select_lv.sendAndLoad(filepath + "getscores.php", select_lv, "GET");

The advantages of using a datagrid to display the data, besides being simple to set up, are that data is automatically put into easily read columns (no tab formatting or monospace font needed, as would be necessary if doing this with a textarea or listbox), and it can be sorted by the user on any of the fields by just clicking on the corresponding column header.

Custom Sort

Because sorting is done alphabetically by default in Flash (in ascii code order), code must be added to handle any other kinds of sorts (case insensitive, numeric, etc). Because the datagrid inherits from the List class, the List's sortItemsBy function can be applied to handle sorting on a particular column. Here is the actionscript to add an object that will listen for header click events on the datagrid and apply a custom sort on the first and second columns (Nicknames, which we want to have a case-insensitive sort, and Score, which we want to have a numeric sort):


// create an object to listen for clicks on datagrid headers to do correct sort
var headerListener:Object = {};
headerListener.headerRelease = function(event:Object) {
   switch (event.columnIndex) {
      case 0:
         if (scores_dg.getColumnAt(0).sortedUp) {
            scores_dg.sortItemsBy(scores_dg.columnNames[0], Array.CASEINSENSITIVE | Array.DESCENDING);
         } else {
            scores_dg.sortItemsBy(scores_dg.columnNames[0], Array.CASEINSENSITIVE);
         }
         scores_dg.getColumnAt(0).sortedUp = !scores_dg.getColumnAt(0).sortedUp;
         break;
      case 1:
         if (scores_dg.getColumnAt(1).sortedUp) {
            scores_dg.sortItemsBy(scores_dg.columnNames[1], Array.NUMERIC | Array.DESCENDING);
         } else {
            scores_dg.sortItemsBy(scores_dg.columnNames[1], Array.NUMERIC);
         }
         scores_dg.getColumnAt(1).sortedUp = !scores_dg.getColumnAt(1).sortedUp;
         break;
   }
}

Adding a record to the high scores table

Normally, a high score would be added to the database at the end of a game, but in this case we'll just tack on a data entry form below the datagrid to allow records to be added (the concept is the same). We'll include a field in the form for every field in the table (except the auto-increment id field, which will be created automatically), do some minimal checking to make sure the data is correct, and then insert the record into the database. If that is successful, we'll update the datagrid to show the new value, and display a success message. Otherwise, we'll display a message showing what was wrong. Here is a script to receive data from the LoadVars object, write it to the database, and return errorcode and msg variables. Much of the error checking is the same as with the script above, so refer to the comment lines in that for explanations. The script also returns an id variable with the record id of the inserted record, which is kept in scoreInfo to use if the record is later selected for deletion.

<?
/*
   insertscore.php:  inserts record into highscores table, returns new 
                     record id and status to Flash
*/

// fill with correct data for your server configuration
$server = "";
$username = "";
$password = "";
$database = "";

if (!mysql_connect($server, $username, $password)) {
   $r_string = '&errorcode=1&';	
	
} elseif (!mysql_select_db($database)) {
   $r_string = '&errorcode=2&';
	
} else {
   $ins_str = "INSERT INTO highscores VALUES (NULL, '".addslashes($_GET['nickname'])."', '".$_GET['dateposted']."', '".$_GET['score']."')";

   if (!mysql_query ($ins_str)) {
      $msg = mysql_error();
      $r_string = '&errorcode=3&msg='.$msg;
   } else {
      // pass back id of inserted record
      $id = mysql_insert_id();
      $r_string = '&errorcode=0&id='.$id.'&';
   }
}

echo $r_string;
?>

The nickname field has PHP's addslashes function applied to it (the same as the escape function in Flash) before writing it to the database, so that any nonalphanumeric characters in it will be preserved for display in Flash, and quotes can be included in the name without causing an SQL error. PHP stripslashes will be run on the field when it is retrieved from the database to return it to its original value.

To communicate with the insertscore script, we'll use another LoadVars instance, insert_lv. The nickname field will be limited to 50 characters (since that is the max in our database) and the score and date fields will have default values put in them. Following is the extra code added to the Flash movie to deal with the addition of a record (with TextInput components nickname_ti, score_ti, date_ti, TextArea component msg_ta, and Button component add_btn added to the movie). Notice that the datagrid dataprovider's addItem method (not push) is used to update both the dataprovider and the display.

// for insert query
var insert_lv:LoadVars = new LoadVars();
// get today's date
var today:Date = new Date();

// function to put a zero in front of a one-digit number
function zerofill(n:Number):String {
   if (n < 10) return '0' + n.toString();
   else return n.toString();
}

// limit fields to max characters allowed by database
nickname_ti.maxChars = 50;
date_ti.maxChars = 10;

// put in some default values
score_ti.text = 100;
date_ti.text = today.getFullYear() + '-' + 
               zerofill(today.getMonth()+1) + '-' + 
               zerofill(today.getDate());

// function to execute after insertscore.php has run
// it returns errorcode, msg (sql error), id (of most recent insert)
insert_lv.onLoad = function(ok:Boolean) {
   if (ok) {
      if (this.errorcode == "0") {
          // update the dataProvider so datagrid updates
          scoreInfo.addItem(
             {record:this.id,
              nickname:nickname_ti.text,
              score:score_ti.text,
              dateposted:date_ti.text
             });
          // reset fields
          nickname_ti.text = '';
          score_ti.text = 100;
          date_ti.text = today.getFullYear() + '-' + 
                           zerofill(today.getMonth()+1) + '-' + 
                           zerofill(today.getDate());
          msg_ta.text = "Score was added to the database";
      } else {
         // show kind of error
         msg_ta.text = errorMsgs[Number(this.errorcode)];
         // if query error, show mysql_error
         if (this.errorcode == "3") msg_ta.text += ": " + this.msg;
	  }
   } else {
      // if loadvars failed (eg, if script not found)
      msg_ta.text = "Flash-database insert operation failed";
   }
};


function insertRecord() {
   // check for no name or bad score
   if (nickname_ti.text == '') {
      msg_ta.text = "Please enter your name";
   } else if (isNaN(score_ti.text) || 
               Number(score_ti.text) < 0 || 
               Number(score_ti.text) > 65000) {
      msg_ta.text = "Score must be a number between 0 and 65000";
   } else {
      insert_lv.nickname = nickname_ti.text;
      insert_lv.score = score_ti.text;
      insert_lv.dateposted = date_ti.text;
      insert_lv.sendAndLoad(filepath + "insertscore.php", insert_lv, "GET")
   }
}

add_btn.addEventListener("click", insertRecord);

Deleting a record from the table

As with selecting and inserting records, we'll create a separate LoadVars instance to handle deleting records: delete_lv, which will call the PHP script to delete a specified record. deletescore.php looks similar to our other php scripts, with the same kind of error checking and a msg returned if the delete query fails:

<?
/*
   deletescore.php:  deletes record for passed id from highscores table and
                     returns status to Flash
*/
// fill with correct data for your server configuration
$server = "";
$username = "";
$password = "";
$database = "";

if (!mysql_connect($server, $username, $password)) {
   $r_string = '&errorcode=1&';	
	
} elseif (!mysql_select_db($database)) {
   $r_string = '&errorcode=2&';
	
} else {
   if ($_GET['id'] > 4) {
      $del_str = "DELETE FROM highscores WHERE id=".$_GET['id'];

      if (!mysql_query ($del_str)) {
         $msg = mysql_error();
         $r_string = '&errorcode=3&msg='.$msg;
      } else {
         $r_string = '&errorcode=0&';
      }
   } else {
      $r_string = '&errorcode=4&';
   }
}

echo $r_string;
?>

To delete a record from the high scores table, we need to know two things: the id associated with that record (so we can delete it from the database), and the index into scoreInfo of that record (so we can remove it from the dataProvider if the database delete is successful). These can be obtained with properties of the List class, which Datagrid inherits from -- specifically selectedItem and selectedIndex. Notice that like the insert, the datagrid dataprovider's removeItemAt method (not splice) is used to update both the dataprovider and the display. This code carries out the deletion in Flash:

delete_lv.onLoad = function(ok:Boolean) {
   if (ok) {
      if (this.errorcode == "0") {
         scoreInfo.removeItemAt(deleteIndex);
         msg_ta.text = "Record was deleted";
      } else {
         // show kind of error
         msg_ta.text = errorMsgs[Number(this.errorcode)];
         // if query error, show mysql_error
         if (this.errorcode == "3") msg_ta.text += ": " + this.msg;
	  }
   } else {
      // if loadvars failed (eg, if script not found)
      msg_ta.text = "Flash-database delete operation failed";
   }
};

function deleteRecord() {
   // remember which element of the array is to be deleted
   deleteIndex = scores_dg.selectedIndex;
   // get the id of the record to delete, to pass it to deleterecord.php
   var id:Number = scores_dg.selectedItem.record;
   delete_lv.id = id;
   delete_lv.sendAndLoad(filepath + "deletescore.php", delete_lv, "GET");
}

delete_btn.addEventListener("click", deleteRecord);

You can download the fla for this sample here. Revisions to make the same sample work with AMFPHP Remoting can be found on the next page.