jump to navigation

From MySQL to jQuery, via PHP, XML & Ajax October 28, 2011

Posted by Tournas Dimitrios in JQuery.

This article will focus on getting data from a database using PHP and converting it to an XML document with XMLWriter (PHP-extension) . The browser will  request  that XML document  through jQuery via Ajax calls . Seems complex, but is in fact , very simple . This article assumes that you know how to connect to a Mysql database which has a table setup called “people” with 3 columns : “title”, “firstname” and “surname”. Please enter some data into this table as it will be required .

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 which will be generated by XMLWriter  :

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<firstname title="Mr">Peter</firstname>
<firstname title="Mr">David</firstname>
<firstname title="Mr">John</firstname>
<firstname title="Mr">James</firstname>

I’ll skip the detailes how to connect and read data from Mysql with PHP , so the next step is to generate an XML-document from the query result . Certainly , XML documents can be parsed using a series of regular expressions . However PHP 5.2+ is optimized for such tasks , it includes tools (extensions) such as :

  • SimpleXML , XMLReader and SAX : For iterating through a document and grabbing only the content you need without ever once writing a regular expression .
  • XMLWriter and DOM : Writing XML-documents and even appending Elements to XML with DOM .

 In this article I ‘ ll use the XfMLWriter extension as it’s simple to use .

mysql_connect('localhost', 'root', '');
$sql= "SELECT firstname, surname , title FROM people ";
$res = mysql_query($sql);
$xml= new XMLWriter();
while ($row = mysql_fetch_assoc($res)) {
$xml->writeAttribute('title', $row['title']);
header('Content-type: text/xml');


Generating documents with XMLWriter is as follows :

  • First we have to instantiate a new XMLWriter Class
  • Starting a new document , setting the version of the XML and the encoding we gonna use .
  • Enabling ” indentation” with setIndent()  so that the result is very clean and formatted XML
  • Creating (starting) XML-nodes is simple as
    $xml->startElement(‘people’) ;
  • Creating closing XML-nodes is simple as
    $xml->endElement() ;
  • Creating attributes inside XML-nodes is simple as
    $xml->writeAttribute(‘title’, $row[‘title’]) ;
  • Writng data into XML-nodes is simple as

And the JQuery code is :

$(document).ready(function() {


var data = "";

Read directly an XML-file on the server or via PHP-Mysql
$.post("theXML.xml", function(theXML){

$.post("thephp.php", function(theXML){


var title = $(this).find("firstname").attr("title");
var firstname&nbsp;= $(this).find("firstname").text();
var surname = $(this).find("surname").text();

data = data + title + " " + firstname + " " + surname + "





Download the zip file , it includes  a .sql  file with some dummy data . Import the data into your database via phpMyadmin .


No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s