Maurice Price

I have been programming web applications for about 10 years, I started in the PHP environment but soon got sick of having to refresh the page all the time, so I moved into Flash programming with ActionScript and have been happily using PHP and Actionscript together ever since. I generally build all the front end forms and interfaces in Flash and using actionscript to populate the components, datagrids, combo boxes etc...

View all articles by Maurice Price

When I first started to build web applications with flash I had a bit of difficulty with the datagrid. I most of my web applications the datagrid is one of the most useful components. However, when you're new the DataGrid can be a little intimidating to get it to produce the results you want. I am going to give you a few tips on how to get a Flash DataGrid component to read data from a MySQL database.

Flash needs to have some form of intermediate file to retrieve the data from the database. I use PHP scripts as my intermediate files.

There are three main steps in populating a Flash DataGrid from a MySQL database.

First Step
Create  your database.
The company you're hosting your website with  most likely has phpMyAdmin available in your administration area. phpMyAdmin is a MySQL database management tool written in PHP. It is easy to use, and you can create and manage your database using this tool.

Second Step
Create the intermediate php script to read and format the data for your Flash DataGrid.
If you're new to Flash then chances are that you're new to PHP, so I will start with the basics.  And point you over to a free PHP script you can copy.

Fist of all you open your script file with the PHP tags. 

<php

Then we include the host file which holds the database host, usename, password and database name.

The reason I keep the database information in a file separate from the PHP script is, if I change the database or the hosting all I need to do is modify the information in the host file, and it changes it for all my php files on the site.

include "host.ini";


Next is the  php mysql connect function
this is the function that connects to the mysql database and enables reading and writing to the database.

mysql_connect($host,$user,$pass);
mysql_select_db($db);


Now we need to build our (Sequencial Query Language) SQL query.

Starting with the Select statement, we then  name the table, we want to extract the data from  and the field within that table.
ie : Customer_Table . field_name.
 
If you're wondering what the $x="Select part of the statement is all about, it's because everything  that is within the "double quotes" is passed to the variable $x so not my entire SQL query is held within $x. This make it much easier to handle.

$x="SELECT
`Customer_table`.`cust_id_num`,
`Customer_table`.`cust_title`,
`Customer_table`.`cust_firstName`,
`Customer_table`.`cust_lastName`,
`Customer_table`.`cust_address`,
`Customer_table`.`cust_city`,
`Customer_table`.`cust_areacode`,
`Customer_table`.`cust_telephone`,
`Customer_table`.`cust_notes`,
`Customer_table`.`cust_date`,
`Customer_table`.`cust_dob`
FROM
`Customer_table`
ORDER BY
`Customer_table`.`cust_lastName` ASC";


We then pass our Select Query that is in $x over to the mysql_query function.

The function retrieves the data by sending the query statement to MySQL and then the MySQL database sends the results back to the mysql_query function and puts the results into the variable $results.


Then we use the list function which is within a loop to read the $results variable which holds the array data, which was sent back from the MySQL database query.

/* The PHP code below formats the database results into an array readable by the Flash
datagrid.*/

$i = 0;
$n = 0;

$results = mysql_query("$x");
$n= mysql_num_rows ($results);
while ($row = mysql_fetch_array ($results)) {
         while (list ($key, $val) = each ($row)) {
           
$r_string .= '&' . $key . $i . '=' . stripslashes($val);
         }
         $i++;
}

echo "&i=" . $i;
echo "&n=" . $n;
$r_string .='&';
echo"&$r_string";

?>


The list function extracts the data and splits up the data into its fields and puts the relevant fields into the relevant variables which correspond to the original query layout.

Then the variables are read out using the echo statement.

The &r_string is used by Flash to retrieve the data into the actionscript code for the datagrid.



Third Step
Populating the DataGrid in Flash

The following actionscript code reads the data from the php file and populates the datagrid. 

// This reads the data from the PHP script and populates the datagrid.
var  sendData = new LoadVars();
var  r_string = new LoadVars();
r_string.onLoad = getResponse; 

sendData.addr = propDgrid.selectedItem.j2
job_id_num=custDgrid.selectedItem.cust_id;
mypath89 =_global.mypath + "Cust_datagrid.php";
sendData.sendAndLoad(mypath89, r_string, "post");

function getResponse(result){
     if (result == true) {
          var r_string = new Array();
          for (var i:Number=0; i < this.n; i++) {
               r_string.push(
                    {  cust_id_num:this["cust_id_num"+i],
                         j1:this["cust_title"+i],
                         j2:this["cust_firstName"+i],
                         j3:this["cust_lastName"+i],
                         j4:this["cust_city"+i]
                    })};
          }
     Cust_list.dataProvider = r_string;
}


This article gives a short explanation of how to populate a Flash DataGrid with data from a MySQL database using a PHP files as an intermediary.

This article shows and explains the basics only, and if you would like the complete tutorial, there is a Free pdf version of how to populate a Flash DataGrid using a PHP script at InteractiveWebConcepts.net

I hope this is of some help, please feel free to ask questions regarding the topic of this  article.

Maurice Price
InteractiveWebConcepts.net