Retrieve data from mySQL with Flash CS3

[Flep]

We have seen how to enter data in a database with Flash CS3.
In that case, we had a form in Flash that sent the data of user to a PHP script that turn them inserted in the database mySQL.

Now we see how to retrieve this data.
As I said earlier, Flash and mySQL can not communicate directly and therefore also in this if we make use of a server-side script (PHP).
In addition we need that the PHP output must be XML format.
So the first thing we need to create a file that is running the PHP Query the database and returns XML output to Flash.

Only at this point we can see data with Flash in ways and forms with which we like.
In this case, I use a DataGrid component.

This tutorial leans on the same database and table "clients" used in the tutorial insert data in a database with Flash CS3.
I create a PHP file that retrieves data from table "clients" and shoots out an output in XML format:

PHP Code:

<?php
    /*
 *************************************
  Retrieve data from DataBase with Flash CS3                 
  http://www.flepstudio.org     
  Author: Filippo Lughi          
  version 1.0                                
 *************************************
 */
 /******** CHANGE YOUR DATABASE SETTING *******/
    $dbhost = 'localhost'; // database host ( usually localhost )
    $dbuser = 'root'; // database username
    $dbpass = 'root'; // database password
    $dbname = 'dbname'; // database name
    $mysql = mysql_connect($dbhost,$dbuser,$dbpass);
    mysql_select_db($dbname);
    
    $Query="SELECT * from clients";
    $Result=mysql_query( $Query );
    $Return="<?xml version=".'"1.0"'." encoding=".'"UTF-8"?>'."\n"."<clients>";
    
    while($client=mysql_fetch_object($Result))
    {
     $Return.="<client><id><![CDATA[".$client->ID."]]></id><name><![CDATA[".$client->name."]]></name><surname><![CDATA[".$client->surname."]]></surname><address><![CDATA[".$client->address."]]></address><city><![CDATA[".$client->city."]]></city><state><![CDATA[".$client->state."]]></state><email><![CDATA[".$client->email."]]></email></client>"; 
    }
    $Return.="</clients>";
    mysql_free_result($Result);
    echo ($Return);
?>

Following is the output that PHP creates:

<?xml version="1.0" encoding="UTF-8"?>
<clients>
	<client>
		<id>1</id>
		<name>filippo</name>
		<surname>lughi</surname>
		<address>piazza fiorentini 17</address>
		<city>cesenatico</city>
		<state>italy</state>
		<email>info@flepstudio.org</email>
	</client>
	<client>
		<id>2</id>
		<name>paolo</name>
		<surname>rossi</surname>
		<address>via verdi</address>
		<city>milano</city>
		<state>italy</state>
		<email>wewew@fddf.com</email></client>
	<client>
		<id>3</id>
		<name>giacomo</name>
		<surname>leopardi</surname>
		<address>via marrone</address>
		<city>roma</city>
		<state>italy</state>
		<email>qwqwqw@gfgfgf.com</email>
	</client>
	<client>
		<id>4</id>
		<name>antonio</name>
		<surname>gialli</surname>
		<address>via viola</address>
		<city>napoli</city>
		<state>italy</state>
		<email>hgfgfd@gfdf.com</email>
	</client>
	<client>
		<id>5</id>
		<name>andrea</name>
		<surname>viola</surname>
		<address>piazza la bomba e scappa</address>
		<city>torino</city>
		<state>italy</state>
		<email>wewewe@dfdfdf.com</email>
	</client>
	<client>
		<id>6</id>
		<name>paolo</name>
		<surname>de paoli</surname>
		<address>via paolino</address>
		<city>paolopoli</city>
		<state>italy</state>
		<email>sdsdsd@fdfdfd.com</email>
	</client>
	<client>
		<id>7</id>
		<name>alessandro</name>
		<surname>magno</surname>
		<address>via da roma</address>
		<city>roma</city>
		<state>italy</state>
		<email>qqwqw@fgfgfg.com</email>
	</client>
	<client>
		<id>8</id>
		<name>briana</name>
		<surname>banks</surname>
		<address>via del padulo</address>
		<city>genova</city>
		<state>italy</state>
		<email>scscssc@ghghggh.com</email>
	</client>
	<client>
		<id>9</id>
		<name>ruggero</name>
		<surname>ruggeri</surname>
		<address>via del rug</address>
		<city>ragusa</city>
		<state>italy</state>
		<email>qwqqw@fdfdfd.com</email>
	</client>
</clients>

Now I create a FLA that contains a DataGrid component in the stage.



I create the Document Class, main.as:

package
{
	import flash.display.*;
	import flash.events.*;
	import flash.net.*;
	import flash.xml.*;
	import fl.controls.ScrollPolicy;
	
	public class Main extends MovieClip
	{
		private var clients_array:Array=new Array();
		private var titles_array:Array;
		
		public function Main()
		{
			addEventListener(Event.ADDED_TO_STAGE,init);
		}
		
		private function init(evt:Event):void
		{
			removeEventListener(Event.ADDED_TO_STAGE,init);
			
			titles_array=new Array('id','name','surname','address','city','state','email');
			
			loadXML();
		}
		
		private function loadXML():void
		{
			var loader:URLLoader=new URLLoader();
			loader.addEventListener(Event.COMPLETE,completeHandler);
			
			var request:URLRequest=new URLRequest('http://www.flepstudio.org/swf/ImmagazzinareDati/recuperare/getData.php');
			try 
			{
				loader.load(request);
			} 
			catch(error:Error) 
			{
				trace('Impossibile caricare il documento.');
			}
		}
		
		private function completeHandler(event:Event):void
		{
			var result:XML=new XML(event.target.data);
			var myXML:XMLDocument=new XMLDocument();
			myXML.ignoreWhite=true;
			myXML.parseXML(result.toXMLString());
			var node:XMLNode=myXML.firstChild;
			var n:int=node.childNodes.length;
			for(var i:int=0;i < n;i++)
			{
				var obj:Object=new Object();
				
				for(var j:int=0;j < node.childNodes[i].childNodes.length;j++)
				{
					if(j==0)
						obj.id=int(node.childNodes[i].childNodes[j].firstChild.nodeValue);
					if(j==1)
						obj.name=node.childNodes[i].childNodes[j].firstChild.nodeValue;
					if(j==2)
						obj.surname=node.childNodes[i].childNodes[j].firstChild.nodeValue;
					if(j==3)
						obj.address=node.childNodes[i].childNodes[j].firstChild.nodeValue;
					if(j==4)
						obj.city=node.childNodes[i].childNodes[j].firstChild.nodeValue;
					if(j==5)
						obj.state=node.childNodes[i].childNodes[j].firstChild.nodeValue;
					if(j==6)
						obj.email=node.childNodes[i].childNodes[j].firstChild.nodeValue;
				}
				clients_array.push(obj);
			}
			
			populateDataGrid();
		}
		
		private function populateDataGrid():void
		{
			my_dg.rowCount=5;
			my_dg.columns=titles_array;
			my_dg.verticalScrollPolicy=ScrollPolicy.ON;
			for(var i:int=0;i < clients_array.length;i++)
			{
				my_dg.addItem(clients_array[i]);
			}
		}
	}
}

We have 3 functions:
loadXML that calls the PHP script.
completeHandler that parse the XML output and insert data into Array.
populateDataGrid that populate the datagrid by using the PHP output.

Source files:

[mrhiggs]

this example is not working.it throws this error to me:

TypeError: Error #1009:
at Main/::completeHandler()
at flash.events::EventDispatcher/flash.events:EventDispatcher::dispatchEventFunctio n()
at flash.events::EventDispatcher/dispatchEvent()
at flash.net::URLLoader/flash.net:URLLoader::onComplete()

[Flep]

Open Main.as and delete all the .nodeValue you find in it.

Otherwise, you should check if a value of the XML is null ( empty ).

[Flep]

I added CDATA to XML.