Posts Tagged ‘SPRY’

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])

Using SPRY to fetch values from a URL

And then filter an XML file based on the values passed from the URL.

If you’re not aware, Adobe now has it’s own AJAX/Javascript library called SPRY for you to use and better yet they include this with Dreamweaver CS3. I assume that you already probably know of or use SPRY.

So lets assume you want to pass in a value via your URL to filter your data set, baring in mind that this will be a destructive filter. So for example your url is mysite.com/index.htm?myFilter=a

And for example your XML file is something like;

description description

For this to work you need to add in/ include these JavaScript files in your HTML file:



// These are needed to interface with the XML file

// This is whats used to get the values from the URL query string

And this is the code, if you’ve already used SPRY then you’ll understand some of this.

And now when you make your call to the data set as normal, it will still function, but when a value is passed into the myFilter variable in the URL, it will then filter the data and return only the matching results.

More can be found here from Adobe