Dynamically Generating XML from a Database
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Note that the code listings in this article assume the existence of a MySQL database and a familiarity with PHP's database access functions (specifically, its MySQL functions). In case you don't already have MySQL, you can download it from http://www.mysql.com/, and SQL dump files for the database tables used in this article may be obtained from this book's companion web site (http://www.xmlphp.com).
I'll begin with a simple example. Let's assume the existence of a table holding information on a personal CD collection. Here's a snippet from the table:
+----+---------------------------------+---------------+ | id | title | artist | +----+---------------------------------+---------------+ | 2 | Get A Grip | Aerosmith | | 3 | All That You Can't Leave Behind | U2 | | 4 | Androgyny | Garbage | +----+---------------------------------+---------------+
Now, the process of converting these rows and columns into XML would break down into the following steps:
Connect to the database.
Retrieve a result set.
Iterate through this result set, and create XML structures corresponding to the data retrieved.
Output the complete XML document.
XML does not possess any database manipulation capabilities. PHP, however, does, and because it also comes with a capable implementation of the DOM, it can be used to accomplish the preceding four steps with minimal difficulty. Listing 1 demonstrates the script that does all the work.
Listing 1—A Dynamically Constructed XML DOM Tree from a Database
<?php
// query database for records
$connection = mysql_connect("cdserver", "joe", "cool") or die ("Unable
to connect!");
mysql_select_db("db712") or die ("Unable to select database!");
$query = "SELECT id, title, artist FROM cds";
$result = mysql_query($query) or die ("Error in query: $query. " .
mysql_error());
if (mysql_num_rows($result) > 0)
{
// create DomDocument object
$doc = new_xmldoc("1.0");
// add root node
$root = $doc->add_root("cds");
// iterate through result set
while(list($id, $title, $artist) = mysql_fetch_row($result))
{
// create item node
$record = $root->new_child("cd", "");
$record->set_attribute("id", $id);
// attach title and artist as children of item node
$record->new_child("title", $title);
$record->new_child("artist", $artist);
}
// print the tree
echo $doc->dumpmem();
}
// close connection
mysql_close($connection);
?>
Much of this should already be familiar to you, but let me take you through it anyway:
The first step is to connect to the database and execute a query to retrieve data from it; this is accomplished using PHP's standard MySQL functions.
$connection = mysql_connect("cdserver", "joe", "cool") or die ("Unable to connect!"); mysql_select_db("db712") or die ("Unable to select database!"); $query = "SELECT id, title, artist FROM cds"; $result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());Assuming that one or more records are returned, the next step is to create an XML document in memory. This is accomplished via the DOM extension's new_xmldoc() function, which returns an instance of the DOMDocument class.
$doc = new_xmldoc("1.0");Next, the document element, <cds>, is generated and added to the document, and a reference is returned to this newly minted node. This reference will be used in subsequent steps to construct the rest of the DOM tree.
$root = $doc->add_root("cds");With the preliminaries out of the way, all that's left is to iterate through the MySQL result set, and create XML representations of the data within it. In Listing 1, every record in the result set is represented as a <cd> element, with the fields within each record represented as attributes or children of this <cd> element.
while(list($id, $title, $artist) = mysql_fetch_row($result)) { $record = $root->new_child("cd", ""); $record->set_attribute("id", $id); $record->new_child("title", $title); $record->new_child("artist", $artist); }After the document has been completely generated, the dumpmem() object method is used to dump the XML tree as a string.
echo $doc->dumpmem();
Listing 2 demonstrates what the output of Listing 1 looks like (note that the output has been manually indented for greater readability):
Listing 2—A Dynamically Generated XML Document
<?xml version="1.0"?>
<cds>
<cd id="2">
<title>Get A Grip</title>
<artist>Aerosmith</artist>
</cd>
<cd id="3">
<title>All That You Can't Leave Behind</title>
<artist>U2</artist>
</cd>
<cd id="4">
<title>Androgyny</title>
<artist>Garbage</artist>
</cd>
</cds>
Backtrack
Still not too clear about how Listing 1 works? Refer to Chapter 3, "PHP and the Document Object Model (DOM)" from the book XML and PHP for detailed information on the topic, or read the article "Using PHP With XML" (http://www.melonfire.com/community/columns/trog/article.php?id=72) on the Melonfire web site (http://www.melonfire.com), things should start making more sense.
X Marks the Spot
You might be interested to hear that version 4.x of the MySQL client application includes the ability to format an SQL result set as well-formed XML. For example, the command
$ echo 'USE db127; SELECT * FROM cds' | mysql -X
would return:
<?xml version="1.0"?> <resultset statement="SELECT * FROM cds"> <row> <id>1</id> <title>Get A Grip</title> <artist>Aerosmith</artist> </row> <row> <id>2</id> <title>Androgyny</title> <artist>Garbage</artist> </row> </resultset>
This XML output may then be saved to a file, or sent to another application for further processing.
The MySQL client application may be downloaded from the official MySQL Web site, http://www.mysql.com/.
You don't have to restrict your activities to a single table, either; it's just as easy to build an XML document from multiple tables, either by joining them or by performing multiple queries at a time. Consider the following revised database schema, which links each CD to a track list stored in a separate table:
+----+---------------------------------+---------------+ | id | title | artist | +----+---------------------------------+---------------+ | 2 | Get A Grip | Aerosmith | | 3 | All That You Can't Leave Behind | U2 | | 4 | Androgyny | Garbage | +----+---------------------------------+---------------+ +----+----------------------------------------+------+ | cd | track | indx | +----+----------------------------------------+------+ | 3 | Beautiful Day | 1 | | 3 | Stuck In A Moment You Can't Get Out Of | 2 | | 3 | Elevation | 3 | | 2 | Eat The Rich | 1 | | 2 | Livin' On The Edge | 2 | +----+----------------------------------------+------+
Listing 3 demonstrates how this information can be represented in XML, extending Listing 1 to include a list of tracks for each CD.
Listing 3—A Dynamically Constructed XML DOM Tree from Two Tables
<?php
// query database for records
$connection = mysql_connect("cdserver", "joe", "cool") or die ("Unable
to connect!");
mysql_select_db("db712") or die ("Unable to select database!");
$query = "SELECT id, title, artist FROM cds";
$result = mysql_query($query) or die ("Error in query: $query. " .
mysql_error());
if(mysql_num_rows($result) > 0)
{
// create DomDocument object
$doc = new_xmldoc("1.0");
// add root node
$root = $doc->add_root("cds");
// iterate through result set
while(list($id, $title, $artist) = mysql_fetch_row($result))
{
$record = $root->new_child("cd", "");
$record->set_attribute("id", $id);
$record->new_child("title", $title);
$record->new_child("artist", $artist);
// add <tracks> node
$tracks = $record->new_child("tracks", "");
// query database for track listing for this CD
$query2 = "SELECT track FROM tracks WHERE cd = '$id' ORDER
BY indx";
$result2 = mysql_query($query2) or die ("Error in query:
$query2. " . mysql_error());
// print each track as a child of <tracks>
while($row = mysql_fetch_row($result2))
{
$tracks->new_child("track", $row[0]);
}
}
// dump XML document to a string
$xml_string = $doc->dumpmem();
}
// close connection
mysql_close($connection);
// print XML
echo $xml_string;
?>
In this case, an additional query has been inserted into the script. This one retrieves a list of tracks for each CD in the collection, and appends this track list to each item in the collection. Listing 4 demonstrates the output:
Listing 4—A Dynamically Generated XML Document
<?xml version="1.0"?>
<cds>
<cd id="2">
<title>Get A Grip</title>
<artist>Aerosmith</artist>
<tracks>
<track>Eat The Rich</track>
<track>Livin' On The Edge</track>
</tracks>
</cd>
<cd id="3">
<title>All That You Can't Leave Behind</title>
<artist>U2</artist>
<tracks>
<track>Beautiful Day</track>
<track>Stuck In A Moment You Can't Get Out Of</track>
<track>Elevation</track>
</tracks>
</cd>
<cd id="4">
<title>Androgyny</title>
<artist>Garbage</artist>
<tracks/>
</cd>
</cds>
Most of the time, this is a good place to stop. After all, the primary goal—to convert database records into XML—has been achieved. This XML can now be saved to a file for later use, parsed or transformed by an XML or XSLT engine, or transmitted over any text-capable communication system. However, it's instructive to see what happens next, if only to gain a deeper understanding of the complete process flow. Take a look at the second article in this series when it posts on July 26 for more.
Emerging from a Cocoon
Once the XML document has been generated, it may be processed and transformed by any XSLT-capable engine. Although PHP does come with a very capable XSLT extension, you can just as easily pass the dynamically generated XML to any other engine for processing.
One example of such an engine is Cocoon, a Java-based application that simplifies the process of publishing XML documents to the web. Fast and scalable, Cocoon is built around the JVM (for portability and performance), SAX (for fast document parsing), and XSLT (for document transformation). It supports content creation in (among others) HTML, WML, and PDF formats.
Cocoon has been developed by The Apache Group, and can be downloaded from http://xml.apache.org/cocoon/.
About This Article
This article is excerpted from XML and PHP by Vikram Vaswani (New Riders Publishing, 2002). Refer to Chapter 7, "PHP, XML, and Databases," for more detailed information on the material covered in this article, or drop by the PHP section (http://www.melonfire.com/community/columns/trog/archives.php?category=PHP) on the Melonfire web site (http://www.melonfire.com/) for more tutorials.