본문 바로가기
프로그래밍/PHP

From MySQL to jQuery, via PHP, XML & Ajax

by o테리o 2011. 2. 26.

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:

01
02
03
04
05
06
07
08
09
10
11
<?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

php-xml

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.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
$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:

1
2
3
4
5
$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

xml-ajax

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:

01
02
03
04
05
06
07
08
09
10
11
<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:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
$(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

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

Learning jQuery 1.3
PHP MySQL Development

PHP MySQL Development
jQuery UI 1.6

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

출처 : http://www.reynoldsftw.com/2009/09/from-mysql-to-jquery-via-php-xml-ajax/
저작자표시 비영리 변경금지

'프로그래밍 > PHP' 카테고리의 다른 글

Twitter OAuth for PHP  (0) 2011.05.10
Installing OAuth on CentOS 5.4  (0) 2011.04.25
PHP로 파일 압축/해제하기 - PclZip Library  (0) 2010.11.25
PHP UTF-8 한글 자르기  (0) 2010.07.27
PHP를 Eclipse로 개발하기 - 설치&설정  (0) 2010.03.19

관련글

티스토리툴바