If you are into making dynamic flash sites, you'll frequently need to query data from a mysql database, using flash as front-end and using php to fetch it. There is no direct way to load complex data structures in flash. So to bring the mysql recordset in flash, we'll need to use some custom functions.

We will use the following 2 files:

1. flash_php.as
2. flash_request_handler.php

To understand the framework, we'll consider a situation where you have to query your database for all the users, where user_lastname = "smith".

Include the flash_php.as in your flash file

#include "flash_php.as";

We'll start with declaring the following variables

// this is the final two dimensional array which will have all the records
// the structure of each record in this array is same as
// the structure of record returned by mysql_fetch_array() in php
user_recordset=new Array();

load_user_list_out = new LoadVars (); // this is the LoadVars object which we will use to SEND the information TO php
load_user_list_in = new LoadVars (); // this is the LoadVars object which we will use to LOAD the information FROM php

And write the following event handler,

// this waits for the data to be returned from php file and then creates a recordset
load_user_list_in.onLoad = function (success) {
    user_recordset = create_recordset(this[_root.outputvar_name]);
    // total_users = user_recordset.length;
    // if you also need to know the total number of users returned, you can uncomment the above line
};

Prepare the data to be sent to flash_request_handler.php

load_user_list_out.outputvar_name = _root.outputvar_name; // follwing 3 lines are mandatory
load_user_list_out.col_delimiter = _root.col_delimiter;
load_user_list_out.row_delimiter = _root.row_delimiter;

// this is the custom variable you want to pass to php
// you can pass as many variables as you need to pass
load_user_list_out.user_lastname = "Smith";

NOTE: One thing I'll like to bring to your attention is, though we can directly pass the SQL query from flash to php by just replacing the line load_user_list_out.last_name = "Smith"; with load_user_list_out.sql = "SELECT * FROM user_table WHERE last_name="Smith"; BUT we will not do this. As, if we write our php code to accept queries directly from flash, we'll be opening a publicly accessible interface to query our database! Anybody who can view your actionscript code (by decompiling) will be able to access that interface.

Thus we'll just stick to passing parameters to PHP and define queries in PHP. In case, your application is just to be used on intranet, you can pass the SQL like load_user_list_out.sql="SELECT user_firstname, user_age FROM user_table WHERE user_lastname='Smith'"; if you want. (you'll need to make minor modifications to the flash_request_handler.php which are mentioned in the php file itself)

Now, time for action

tell_server("load_user_list");

NOTE: Naming is very important here, when you send the value "load_user_list" to tell_server() function, it automatically assumes that the loadVars object which has the data to be sent to php is load_user_list_out and the loadVars object which will be used to being in the data is load_user_list_in.

After this, data will be loaded from php, parsed and your record set array user_recordset will be populated. You can iterate over it like this

for (i=0; i<user_recordset.length; i++) {
    trace ("---record:" add i);
        for (j=0; j<user_recordset[i].length; j++) {
        trace ("column " add j add ":" add user_recordset[i][j]);
    }
}

For all this magic to happen, the PHP has to be modified too. Open the flash_request_handler.php. The first change you need to make is you specify your mysql hostname, loginname and password.

After those changes, you have to specify a case for every type of query you intend to make. Right now, we are querying user_table and loading user_firstname & user_age where all the user_lastname matches the user_lastname we are sending from flash. Please open the PHP file and check the switch..case structure, it's pretty self-explanatory.

Drawback in this framework:

The only drawback currently is that it does not return an associative array, so we have to access the record rows like:

user_recordset[0][0], user_recordset[0][1]
user_recordset[1][0], user_recordset[1][1]
.
.
user_recordset[n][0], user_recordset[n][1]

instead of being able to access it like:

user_recordset[0]['user_firstname'], user_recordset[0][user_age]
user_recordset[1]['user_firstname'], user_recordset[1][user_age]
.
.
user_recordset[n]['user_firstname'], user_recordset[n][user_age]