Please Note: This article has been updated and reposted here
Back To Basics
So this is the first of many new tutorials I hope to provide under the new banner of “Back to Basics”. I hope to be able to provide high level tutorials to the beginner enabling you to take away key learning to build on, rather than give you everything in depth on a plate. I hope you find this insightful.
So today’s article will focus on getting data from a database using PHP, converting that to an XML document, and reading that XML in through jQuery via Ajax calls. Seems complex, but is in fact, very easy.
Database Design
This tutorial assumes you know how to connect to your database. It also assumes that you have a table setup called “people” with 3 columns: “title”, “firstname” and “surname”. Please enter some data into this table as it will be required.
The XML structure
So, the point of this tutorial is to read some XML with an Ajax call from jQuery, therefore we need to structure our data correctly. Here is an example of the structure our XML document will generate:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <people> <person> <firstname title="Mr">Steve</firstname> <surname>Reynolds</surname> </person> <person> <firstname title="Mr">David</firstname> <surname>Grohl</surname> </person> </people>
I won’t go into anymore details other than that is what our PHP script will generate for us for each row entry in the database.
The PHP
First up, we’ll get the data from the database, and then iterate through each row result to generate the XML for that particular entry. We’ll do that by creating a data variable and appending the new XML to it which we will use later. I have not included the database connection part to this, you need to do that part yourself.
$query = "SELECT title,firstname,surname FROM people";
$result = mysql_query($query);
$xml = "";
while($array = mysql_fetch_array($result)) {
$title = $array['title'];
$firstname = $array['firstname'];
$surname = $array['surname'];
$xml .= "<person>";
$xml .= "<firstname title=\"$title\">$firstname</firstname>";
$xml .= "<surname>$surname</surname>";
$xml .= "</person>";
}
Next up we need to form that into an XML file. The way we do this is tell the PHP file to respond with an XML type header, therefore anything that is echoed out will be interpreted by the browser as XML. So after the code above, add this:
header('Content-Type: application/xml; charset=ISO-8859-1');
echo "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>";
echo "<people>";
echo $xml;
echo "</people>";
So, the header part tells the browser that this is an XML content type, and the rest is simply echoing out the XML data structure as mentioned previously.
Hopefully now, when you run that PHP script you should return a valid XML document…
Ajax with jQuery
The last piece to this puzzle is getting that XML data into an HTML page using jQuery and ajax. Again, it’s pretty easy to do, here is the basic HTML structure I am using for this tutorial:
<html> <head> <title>The HTML</title> <script type="text/javascript" src="jquery-1.3.1.min.js"></script> <script type="text/javascript" src="thejs.js"></script> </head> <body> <div id="container"></div> <input type="submit" id="getData" name="getData" value="Get Data!" /> </body> </html>
You need to obviously include the jQuery library, I am also including an external javascript file for our jQuery code. So go ahead and create a javascript file called thejs.js and put the code below into it:
$(document).ready(function() {
$("#getData").click(function(){
var data = "";
$.get("thephp.php", function(theXML){
$('person',theXML).each(function(i){
var title = $(this).find("firstname").attr("title");
var firstname = $(this).find("firstname").text();
var surname = $(this).find("surname").text();
data = data + title + " " + firstname + " " + surname + "<br>";
});
$("#container").html(data);
});
});
});
So this code is firstly wiring up a click event on a button, and then making a GET Ajax call to the PHP script we made earlier. Once it receives some XML data it iterates through each “person” node.
Notice, I am returning two different data sources from the XML, the data in between the <firstname> and <surname> tags, as well as also returning the data for an XML node attribute, in this case “title”. This doesn’t really make much sense in this context, but what it does show you is how you can access attribute information with the function.
Download
Download
So that’s it! You can see a working demo of this here. Download all the sourcecode by clicking the box icon on the left hand side. The code is fully working, you just need to add your database connection code to the PHP script. Feel free to leave feedback and comments in the section below!
Tools to help you learn…
 Learning jQuery 1.3 |
 PHP MySQL Development |
 jQuery UI 1.6 |
Similar Articles:
From MySQL to jQuery, via PHP, XML & Ajax
Using jQuery and Ajax To Create PHP Sessions
CSS Positioning Techniques for Nested DIVs
A how to guide - Live Email Validation
jQuery - PHP & Ajax with the Twitter API
Exactly what I was looking for ! I got a project where that would fit perfectly.
Quick question tho : do we know if manipulating the XML file would be better from a performance standpoint than querying a MySQL ?
Thanks
Greg
Cool.
Might not work as expected if the short tags () are enabled in PHP.
echo “” might be better written as :
echo “”
This is an excellent post and exactly what I am looking for. But I am afraid, one may not able to implement the feature after reading the post. It requires significant knowledge about creating/connecting with the database. It would be wonderful if you could provide link to some other sites which provide that understanding or you could write a follow up post on how to do it. Thanks…
great article for jquery newbie like me ! thanks
HI Steve,
I should say that I am impressed. A real cool tutorial..
A quick doubt though..
The ajax call happens at :
$.get(“thephp.php”, function(theXML){
I am not sure where the returned data from PHP is stored. Is it stored in “theXML” ?
certainly is… theXML is the returned data (xml object) from the PHP in a javascript variable.
Thanks a lot ! just what i’ve been looking for. Wasn’t sure how to insert header
Hey Steve, just wanted to say that this tutorial is exactly what I’ve been looking for. I’m not a php coder, and I’m looking to parse database-driven xml in actionscript — so this is perfect.
Thanks!
OMG THANK YOU!
I know little about PHP – been trying to figure out why I can’t parse the XML response as XML – turned out all I needed all along was that single “header()” line!
Thanks a bunch for putting this info out there.
Thanks for the tutorial: php2xml.
Is it possible to send a server-side include with the getData request? I am trying to access only one row from the database. Using your example database, say I only wanted to return David Grohl.
Very helpful….It helps me in my current peoject where a PHP generates a XML, and then a jQuery image gallery fetch that XML.
Great subject for your tutorial.. but I must admit I don’t get why the while loop runs.. The function $array is never called except in that statement.. I’me a little lost..
The while loop runs through each returned result from the database. If there is only 1 returned result, then yeah – it’s pointless – but there might be 50 results, therefore the while loop runs through each row result and does what it needs to do by spitting out XML
@SteveReynolds
Saw your PHP XML codes on http://www.reynoldsftw.com/2009/03/tutorial-from-php-to-xml-to-jquery-and-ajax/ . It was so helpful. Please, after extracting the data to an XML file, I need to query the XML file to extract a subset of the data. Can you throw more light on how to do this please.
Nice tutorial.It helps me in my project . Thanks a lots…..