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
    /*
 *************************************
  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.

[royald]

what if you don't want to use a datagrid to display e.g. guestbook, how do you go about that with paging (next/previous)?
Thanks.

[Flyingfish]

I am trying to use this to retrieve data from one of my own tables but the datagrid is empty when I view the file.

I know that the php is outputting the correct xml, so it must be something in the main.as file. I reduced the titles array to only three titles in the init function and did the same for the completehandler function.

[creepyflavour]

i have the same problem as mrhigg:

TypeError: Error #1088: The markup in the document following the root element must be well-formed.
at Main/completeHandler()
at flash.events::EventDispatcher/dispatchEventFunction()
at flash.events::EventDispatcher/dispatchEvent()
at flash.net::URLLoader/onComplete()


i did what you said and removed all the .nodeValue , but still the same result.

[phi]

Can you post this exercise in AS3? I think the XML will be a lot easier to understand than using lines like '

node.childNodes[i].childNodes[j].firstChild.nodeValue'

[atcaro]

I had a problem the first time that I dowloaded the tutorial files, but in a second tried they work...

[arthram]

How to delete a record from datagrid which has data from Mysql database?

Please help its little bit urgent