One of my side projects (well many of them now that I think of it) requires me to load data from a database on server into Flash. Back in the old days. and with simple data, I might have done something like:
echo ‘name=’.$name.’&email=’.$email;
… and so on and then load that string into a Flash LoadVars object, on in AS 3 a URLVariables via a URRequest.
This is ok for small simple data, but not so much for large amounts of data requiring a complicated string concatination.
Then one day it dawned on me that you can use the PHP DomDocument object to create a well formatted XML document and skip the whole string concatenation altogether. Furthermore, Flash makes loading external XML from a URL a very simple thing. Hence I could skip the whole LoadVars experience as well.
After doing this once or twice, I also realized that you could pretty much automate the whole process of generating the XML document inside the while loop for fetching the database rows. Essentially what I end up with is an XML document with tag names that match the column names of the table I am performing the query on with the node values matching the value for that row of the query.
Below is a sample of this and the output it produces with some of the more sensitive details removed:
PHP:
<?php
header (”content-type: text/xml”);
/*Create a DomDocument Object*/
$dom = new DomDocument(’1.0′);
$dom->formatOutput = true;
/*Create a root element for the XML document*/
$root = $dom->createElement(’stores’);
$dom->appendChild($root);
mysql_connect(’localhost’,'username’,'password’) or die(’Could not connect to db server’);
mysql_select_db(’database_name’) or die(’Could not select DB’);
$sql = ‘SELECT * FROM table_name’;
$call = mysql_query($sql);
while($row = mysql_fetch_assoc($call)){
/*For each row returned, create a node to represent that row*/
$store = $dom->createElement(’store’);
$root->appendChild($store);
/*Loop through the returned row and create a node that matches the column name and use the value for the node’s value*/
foreach($row as $key=>$val){
$col = $dom->createElement($key,stripslashes($val));
$store->appendChild($col);
}
}
/*echo out the XML to the page*/
echo $dom->saveXML();
?>
Output (edited for sensitive data, but representative) :
<stores>
<store>
<companyName>Diamond Imports</companyName>
<companyDesc>
Diamond Imports is one of Australia’s largest diamond importers specializing in loose diamonds, certified diamonds, wholesale diamonds, excellent cut diamonds and ideal cut diamonds. Diamond Imports is 100% Australian owned and operated company based in Sydney.
</companyDesc>
<companyAddress>Bridge Street</companyAddress>
<suburb>Sydney</suburb>
<city>Sydney</city>
<state>NSW</state>
<AverageRatingGuarantee>5</AverageRatingGuarantee>
<AverageRatingSelection>5</AverageRatingSelection>
<AverageRatingHours>5</AverageRatingHours>
<AverageRatingService>5</AverageRatingService>
<AverageRatingAmbience>5</AverageRatingAmbience>
<AverageRatingCertification>5</AverageRatingCertification>
<AverageRatingKnowledge>5</AverageRatingKnowledge>
<AverageRatingImpression>5</AverageRatingImpression>
</store>
</stores>
To load this into flash or flex, all I do is point to the URL of the page that generates the XML:
var storeData:XML = new XML();
storeData.ignoreWhite = true;
storeData.load(”getStores.php”);