15th Apr 2008

Using PHP’s DomDocument to Generate XML from an SQL Query

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”);

2 Responses to “Using PHP’s DomDocument to Generate XML from an SQL Query”

  1. doug Says:

    Nice tutorial!
    Im kind of new to php, I was wondering if you or someone could explain what -> means. You used it with
    $dom->formatOutput = true;

    $root = $dom->createElement(’stores’);

    $dom->appendChild($root);

    and some other things.

  2. Jeremy Wischusen Says:

    The -> is PHP version of dot syntax so for instance if this was javascript that would be something like dom.formatOutput= true and dom.appendChild(root)