Back in the early part of this year I posted an article around how to get MySQL data out of the database and into a web page via jQuery and Ajax. The tutorial was okay, but I made some rookie mistakes – specifically around the creation of XML data with PHP – This time, I hope to rectify that!
So this 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 using the PHP function DOMDocument() which creates an XML document for us, and also allows us to add nodes and children to the XML on the fly.
$query = "SELECT title,firstname,surname FROM people";
$result = mysql_query($query);
$doc = new DomDocument('1.0');
// create root node
$root = $doc->createElement('people');
$root = $doc->appendChild($root);
while($array = mysql_fetch_array($result)) {
// add node for each row
$occ = $doc->createElement('person');
$occ = $root->appendChild($occ);
$child = $doc->createElement('firstname');
$child = $occ->appendChild($child);
$child->setAttribute('title', $array['title']);
$value = $doc->createTextNode($array['firstname']);
$value = $child->appendChild($value);
$child = $doc->createElement('surname');
$child = $occ->appendChild($child);
$value = $doc->createTextNode($array['surname']);
$value = $child->appendChild($value);
}
Next up we need to form that into an XML file. The way we do this is tell the PHP file to save the XML document with the saveXML() function, get the PHP script 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:
$xml_string = $doc->saveXML();
header('Content-Type: application/xml; charset=ISO-8859-1');
echo $xml_string;
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:
Tutorial: From PHP to XML to jQuery and Ajax
Using jQuery and Ajax To Create PHP Sessions
CSS Positioning Techniques for Nested DIVs
Live MySQL Database Search with jQuery
jQuery - PHP & Ajax with the Twitter API
Nice example, thanks.
Personally I prefer JSON, have you looked at that?
To prepare/send variables $foo, $bar and $bazz on the PHP side all you need to do is:
and on the jQuery side:
$.getJSON(‘thephp.php’, function(result) {
var foo = result.foo;
var bar = result.bar;
var bazz = result.bazz;
});
And the variables can be numbers, strings, arrays, objects..
Ehr, no tags, I see
Let me rephrase:
To prepare/send variables $foo, $bar and $bazz on the PHP side all you need to do is:
echo json_encode(compact(‘foo’, ‘bar’, ‘bazz’));
Why would you even bother with the XML step? Why not simply export your data as JSON from PHP? It’s far more compact….
If people want to use JSON, that’s great too, but this article is for using XML – JSON might be better, but that’s another piece…
Nice info! Thanks.
great post
Thanks Sarfraz and everyone else that is enjoying this article.
Thanks for this, but I also wonder why this XML step is for? I also prefer the JSON approach.
Is there a way to convert php timestamps like 40523862 to a normal looking time like 14:22 with jquery?
nice post, thanks!
Hi,
Converting a unix timestamp with javascript is easy (it’s not a jquery thing). See below example where “unix_time” is your timestamp variable:
var date = new Date(unix_time*1000);
var hours = date.getHours();
var minutes = date.getMinutes();
var time = hours + ‘:’ + minutes;
nice info thx!
I loved the tut! An extremely practical solution unlike stand alone tut’s.
One thing I think more people are getting into is either insert’ing or update’ing MySQL tables without a screen refresh, then having the values changed in the webpage table without the refresh.
i.e. a table of data in rows and each row’s is editable, and when update is clicked the data is updated in MySQL and the data in the edited row, without a screen refresh.
How difficult is this to do?
Thanks,
Barton.
Hi,
Is the xml file actually created? I guess my question is do I need to give write access to Apache for this?
Maybe you understand from my question that I misunderstand something and what it is.
I’m asking because I get an ‘thexml.xml’ 404 Not Found. (I did not upload the file that came with your zip file)
Best regards . Jon
I guess I should have looked closer at the comments in your code
. Great work and thanks!
/jon
Great help to my Ajax learning… thankyou!
As a traditional database query to form guy, I am unsure what the purpose of the XML stage is…it seems to be adding an extra unnecessary step..Is the XML included to show how to include that technology or is there an advantage?
What would you use the XML for? To minimize server trips by repeatedly querying the XML clientside? Or what?
Thank you.
Simone
Hi – If you want to use jQuery with Ajax, you need to receive either XML or JSON ideally, you can GET HTML generated by PHP if you want though.
This is just a demo of how you could theoretically pin it all together, if you needed to.
Thanks for writing the tutorial that first made sense to me with regard to reading XML. I used PHP, but just quick to generate custom XML for a mockup. I play with more fake information than real.
im looking for a json php xml mysql web service….know of any code snippets out there?