Send and Load data from flash to mySql via PHP and display the results in Flash

[jwforguson]

In this tutorial I am going to explain how to set up a flash file and php file to send variables to a php file, query a mySql database with the PHP file, and then display the results of that query in Flash.  Not only will I show you how to do this sometimes complicated task, but I will show you how to do it all in 1 frame!

Note:  This tutorial assumes that you already know the basics about flash and actionscript 2.0, PHP scripting, and have allready set up a MySql database.

For this tutorial I am going to use LoadVars.sendAndLoad.


Step1: Creating your PHP file

1. Create a new PHP file with the following code and name it "search.php" (or whatever you want).

Code:

<?
$fieldavalue= $_POST['variableafromflash']; 
$fieldavalue= $_POST['variablebfromflash'];

 This is how you get data from flash variables.  $Team is the variable that the php  file will use within the php file.  $_POST['variable'] is the  variable being passed from flash along with the method that it was sent from flash.  If in your actionscript you use a .sendAndLoad using the the "GET" method, your php script will look like "$phpvalue=$_GET['variablefromflash'] "

Code:

$connect = mysql_connect("servername", "username", "password")

assign database  connect info to a php variable called $connect

       

Code:

mysql_select_db("annex_db", $connect);

connect to the database

       

Code:

$result = mysql_query("SELECT * FROM table where fielda= '$fielda value' AND fieldb= '$fieldb variable");

the above line queries the MySql database to select all data from the table  where fielda is equal to the value of the variable that was passed from flash.  Same thing for fieldb.  It then assigns this query string to the php variable $result

       

Code:

$rows = 0;

assigns the value 0 to the variable $rows.  This is used when there is a possibility of displaying multiple lines of data.  For instance, if you had an inventory of computer systems in your database there might be the possibility that you would want to display all computer systems, and the equipment that goes with it, of a certain model.  Using this $rows, as you will see below will allow you to display all of the information for that system on one line, and then all of the information for another system of the same model but a different serial number on the next line

Code:

  while($row=mysql_fetch_array($result)){

use the variable $result to fetch our results based on the parameters
given and display as an array

Code:

echo "Fielda$rows=$row[Field1's database value]&Fieldb$rows=$row[Field2's database value]&;

$rows++; //increment our row count
}

display the data in string format.  Data will look like  "Fielda0=whatever&Fieldb0=whatever&"  if only 1 row of data is returned.  If more than 1 row is returned, the data will look like  "Fielda0=whatever&Fieldb0=whatever&Fielda1=whatever&Fieldb1=whatever&"etc.  Notice the 0's and 1.  These numbers tell flash which row the data belongs on.  That way flash will assign all data with the 0's to be on the same line and all the numbers with the 1's to be on the next line.
     

Code:

echo "rows=$rows";

after all row count increments are complete, display the total # of rows

Code:

if($result) echo "&writing=Ok&"; 
?>

used by flash for the if/else statement for checking the completion of the "onLoad" statement of our loadVars.sendAndLoad.  Basically says that if the query executed properly, echo "&writing=Ok&"                                 

      
Step2:  Creating your Flash File

1.  Create 2 layers.

2.  Name your top layer "Code" or "Actions" or whatever you want as long as it helps you remember that only actionscript goes on this layer.

3.  Name your bottom layer whatever you want as long as it helps you to remember that only your flash objects go here.

4.  Place your objects onto the scene making sure they are both in the same frame (frame1 for this tutorial).  You can use whatever object you want to initiate the loading of your variables.  Me personally, I used a button.  I also have a dynamic text box.  The button has an instance name of "button_mc" and the dynamic textbox has an instance name of "dynamictext_txt".

Note:My dynamice textbox was set as multiline and renders text as html.

5.  Click on Frame1 in  the "Actions" layer and insert the following actionscript:

Code:

myData = new LoadVars()

create a new variable called myData

Code:

myData.ref = this;

states that the myData variable will reference the data loaded to the flash file

m

Code:

yData.onLoad = function(){ 
if(this.writing=="Ok") {

checks the output of the php file "search.php" to see if writing=Ok. If so, do the following.  If not, do everything after the "else" statement

Code:

for(var i=0;i<this.rows;i++){

remember the $rows in our php file?  This is where php uses the value of that variable (0's and 1's) to organizae the data into separate rows.  This line says to create a variable "i" and make it's value "0".  Then compare "i" to the value of the $rows variable that was output from the php file which looks like "rows="x (x representing a #)".  Then increment "i" to the next higher number AFTER completing the following code.
                 

Code:

dynamictext_txt.htmlText = "<b>"+"Fielda:   "+"</b>"+this["Field a value from php"+i]+"<br>"+"<b>"+"Fieldb:   "+"</b>"+this["Fieldb value from php"+i]+"<br>;

this formats your data from your php file into a nice viewable format.  This will look like:

Fielda:   whatever
Fieldb:  whatever
      

Code:

}
} else { //if "writing" did not equal "Ok"
gotoAndPlay(_currentframe);
}
}

play the current frame again which in turn, makes flash check the "if this.writing="Ok"" statement again
   

6. Now click on the object (in my case the button) that you are using to initiate the sending/loading of your variables and add the following actionscript to that object

Code:

on(release){
myDataOut = new LoadVars()

create a new variable called myDataOut

Code:

myDataOut.variableafromflash = "something";
myDataOut.variablebfromflash = "something";
myDataOut.sendAndLoad("search.php.php",myData,"POST")
}

assign a value to your variable.  If you were using an input textbox it would look like "myDataOut.variableafromflash=inputtextbox.text;



Note:  I used the on(rollOver) event for my dynamice text box.  If you decide to do this, it would be a good idea to do an on(rollOut) that looks something like this:

Code:

on(rollOut){
dynamictext_txt.htmlText = "";
}

This way, the text box clears when you roll off of the icon.  Do whatever you want, this was just a personal preference of mine.


With the actionscript and php scripts above, you can send and load data from flash to php for the purpose of php running a query on a mySql dbase and then display the results of that query back into flash.  The if/else statement (specifically the else statement) allows you to set up a loop to allow the data to load without having to have additional frames on your flash file.  I find this really useful since I get confused easily.

Thanks a lot to Ronald Wernecke and Musicman for their help in my endeavors.

I hope that you have found this tutorial helpful and happy coding.