Creating XML from PHP/ MySQL for use with SPRY

So lets say for example you have a table in your database and you want to return some results, pretty easy with PHP/ MySQL and I dare say easy enough with other languages.

Now rather than return the results to the page and format them etc… you want to instead put them to an XML file – why? Well because creating a dataset in XML allows you to make use of various things both from a design point of view and from the prespective of user interaction. I’m of course talking about AJAX here (but also useful for XSLT etc…).

So assuming you are using PHP5, MySQL5 you can use the template below to do this. I’ve also used Adobe’s SPRY here, but jQuery or straight forward Javascript is easy enough. I’m going to assume you are also using SPRY – but there’s an example at the bottom using plain old javascript.

First stage: Create PHP page to build XML from database query

So this is my page: queryBuilder.php

It takes a parameter parsed via the URL GET method (q) and then loads the data based on this. Using this you can create a lot of different XML datasets served from just one page. Replace [text] with your own values.

createElement(‘[parent node]’);
$root = $doc->appendChild($root);
//create a document header to tell browsers what type of file this is
header(“Content-type: text/xml”);
//fetch data from your database table
$myList = mysql_query(“SELECT
ID,  Name
FROM mytable
ORDER BY Name ASC”);
// process one row at a time
while ($row = mysql_fetch_assoc($myTrades)) {

// add node for each row
$occ = $doc->createElement(‘[child node name]’);
$occ = $root->appendChild($occ);

// add a child node for each field
foreach ($row as $fieldname => $fieldvalue) {
$child = $doc->createElement($fieldname);
$child = $occ->appendChild($child);
$value = $doc->createTextNode($fieldvalue);
$value = $child->appendChild($value);
} // end foreach
} // end while

// get completed xml document
$xml_tradesList = $doc->saveXML();
//return xml page to the browser
echo $xml_tradesList;
} //end if q=

} //end if isset

//close db connection
mysql_close();
?>

Second stage: Create page to filter and display data.

So we can now fetch our dataset and use some javascript/AJAX call to get the XML. For simplicity I’m going to use SPRY, Adobes library, but you can just as easily use jQuery.

Creating a page called queryBuilder.htm first thing is to add in the framework libraries that you need available from Dreamweaver install or free from Adobes site (as linked):


Next we need to define our datasets – if you’re used to SPRY this should be easy enough for you.

So thats essentially it, SPRY calling in records from your database via a PHP page. Of course I’m assuming that you know how to display records in SPRY? No? Well here’s how to do it quickly for a select menu!

//declare a region for SPRY

//declare a region that will repeat all results in a loop

Easy huh?

Alternative to SPRY do it in Javascript

So with just Javascript a chunk of code such as below will do much the same thing as the SPRY code just you have to write more.

Make a file queryBuilder.js

//create variable to store the output of queryBuilder.php
var xmlhttp

// function to create an XML object
function GetXmlHttpObject()
{
if (window.XMLHttpRequest)
{
// code for IE7+, Firefox, Chrome, Opera, Safari
return new XMLHttpRequest();
}
if (window.ActiveXObject)
{
// code for IE6, IE5
return new ActiveXObject(“Microsoft.XMLHTTP”);
}
return null;
}

function showList(str)
{
xmlhttp=GetXmlHttpObject(); //function defined below
if (xmlhttp==null)
{
alert (“Your browser does not support AJAX!”);
return;
}
var url=”queryBuilder.php”;
url=url+”?q=”+str;
//this fetches the XML data in the DOM from the queryBuilder.php page
xmlhttp.onreadystatechange=stateChanged;
xmlhttp.open(“GET”,url,true);
xmlhttp.send(null);
}

function stateChanged()
{
//if XML has been loaded and is ready
if (xmlhttp.readyState==4)
{
// display the xml from queryBuilder.php to this HTML element
document.getElementById(“[Some HTML element]”).innerHTML=xmlhttp.responseText;
}
}

So now you can include this on your html page and call in the XML by calling the js function showList([data to load])

3 Comments

  • Larry – what pieces would be modified to keep checking the database for changes to the query results every few seconds? any more pointers/samples on how to do so? it seems to be the piece i’ve missed in searching this topic out everywhere.

    the link below shows how to get spry to check its xml file source periodically. but i’m stuck on what to do to get the database re-queried and the xml file updated regularly:

    http://labs.adobe.com/technologies/spry/articles/data_set_overview/

    • Hi Jeff,

      To get the database to refresh periodically you would need to run your PHP script from the server on a scheduled task/ cron job. Some web hosts allow you do this. But thats if you want to refresh once an hour or something

      Although everytime you call your XML in the example its actually calling that PHP page and running the query each time so its currently realtime but your browser is caching the result so you don’t see the updates.

      However with XML there is caching and a ‘time to live’ (TTL) before the content expires in the browser. If these values aren’t set in the XML output of the PHP page then the XML object will be cached so you won’t see the change.

      To alter this you can add in the XML parameters (I think) for TTL much like an RSS feed. But you can also force the XML to always render from the server and not the browser cache by adding a time stamp to the query URL so the browser always sees it as a unique file therefore it will always update.

      So try changing the URL you query in the ajax/spry call to:

      var timestamp=getTime();
      var url=”queryBuilder.php”;
      url=url+”?q=”+str+”&ts=”+timestamp;

      Then the query is always unique – I think and that *should* force the browser to avoid using the cache, although that may mean it starts caching each new request.

      Anyway hope that helps you out, I can’t currently test my theory as I’m at work.

      Larry

  • thanks Larry will check this out.

Join the Discussion

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>