XML and PHP [Electronic resources] نسخه متنی

Exporting Database Records To XML

As the web moves to an XML-based paradigm, one of the most common problems faced by developers involves converting legacy data, usually stored in a database, into corresponding XML data. With this in mind, let''''s look at some practical examples that demonstrate how data stored in an SQL-compliant database system can be converted into well-formed XML.

Note that the code listings in this section 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 section may be obtained from this book''''s companion web site.

Dynamically Generating XML from a Database

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 7.1 demonstrates the script that does all the work.

Listing 7.1 A Dynamically Constructed XML DOM Tree from a Database

<?php 
// query database for records 
$connection = mysql_connect
("cdserver", "joe", "cool") or die 
("Unable toconnect!"); 
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 7.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 7.2 demonstrates what the output of Listing 7.1 looks like (note that the output has been manually indented for greater readability):

Listing 7.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> 

$ echo ''''USE db127; SELECT * FROM cds'''' | mysql -X 

<?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> 

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 7.3 demonstrates how this information can be represented in XML, extending Listing 7.1 to include a list of tracks for each CD.

Listing 7.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 7.4 demonstrates the output.

Listing 7.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 goalto convert database records into XMLhas 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.

Transforming Dynamically Generated XML with XSLT

The most common use of this dynamically generated XML usually involves transforming it into some other format via an XSL Transformation. I will do just that by using the very simple XSLT stylesheet illustrated in Listing 7.5.

Listing 7.5 An XSLT Stylesheet Displays a Table of CDs and Tracks (cds.xsl)

<?xml version="1.0"?> 
<xsl:stylesheet version="1.0" xmlns:xsl=
"http://www.w3.org/1999/XSL/Transform"> 
<!-- set up page template --> 
<xsl:template match="/"> 
<html> 
<head> 
<basefont face="Arial" /> 
</head> 
<body> 
<h3>My CD Collection</h3> 
<table border="1" cellspacing="0" cellpadding="5"> 
<tr> 
<td align="center">Artist</td> 
<td align="center">Title</td> 
<td align="center">Track list</td> 
</tr> 
<xsl:apply-templates /> 
</table> 
</body> 
</html> 
</xsl:template> 
<!-- look for CDs --> 
<xsl:template match="//cd">   
<tr> 
<td align="center" valign="top">
<xsl:value-of select="artist" /></td> 
<td align="center" valign="top">
<xsl:value-of select="title" /></td> 
<td align="left" valign="top"> 
<ol> 
<!-- iterate through track list, print each
 <track> element as list item --> 
<xsl:for-each select="tracks/track"> 
<li><xsl:value-of select="." /></li> 
</xsl:for-each> 
</ol> 
&#160;</td> 
</tr> 
</xsl:template> 
</xsl:stylesheet> 

Listing 7.6 uses PHP''''s XSLT processing functions to combine this stylesheet with the dynamically generated XML output you saw in Listing 7.4.

Listing 7.6 Dynamically Constructing and Transforming an XML DOM Tree

<?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); 
// this time, don''''t print the XML 
// instead, create an XSLT processor and transform it into HTML 
if ($xml_string) 
{
// XSLT stylesheet 
$xslt_file = "cds.xsl"; 
// create the XSLT processor 
$xp = xslt_create() or die("Could not create XSLT processor"); 
// read in the XSLT data 
$xslt_string = join(", file($xslt_file)); 
// set up buffers 
$arg_buffer = array
("/xml" => $xml_string, "/xslt" => $xslt_string); 
// process the two files to get the desired output 
if($result = xslt_process
($xp, "arg:/xml", "arg:/xslt", NULL, $arg_buffer)) 
{
// print output 
echo $result; 
} 
else 
{
// else display error   
echo "An error occurred: " . xslt_error($xp) . "(error code " . 
xslt_errno($xp) . ")"; 
} 
// free the resources occupied by the handler 
xslt_free($xp); 
} 
?> 

The first part of Listing 7.6 is identical to Listing 7.3. It queries the database, retrieves track and title information, dynamically generates an XML document using PHP''''s DOM functions, and stores it in a string variable.

After the document has been generated, the focus shifts to PHP''''s XSLT processor, which is initialized with the xslt_create() function:

$xp = xslt_create() or die("Could not create XSLT processor"); 

Then, the XSLT stylesheet is read into a string variable, and both XML and XSLT strings are stored in the array $arg_buffer as named arguments:

$xslt_string = join(", file($xslt_file)); 
$arg_buffer = array
("/xml" => $xml_string, "/xslt" => $xslt_string); 

This argument buffer is then passed to the XSLT processor via xslt_process(), and the result of the transformation is then printed to the browser:

if($result = xslt_process
($xp, "arg:/xml", "arg:/xslt", NULL, $arg_buffer)) 
{
// print output 
echo $result; 
} 

Figure 7.1 shows what the output looks like.

Formatting Dynamically Generated XML With SAX

After you understand the basics, it''''s possible to apply the techniques demonstrated in the preceding examples to do some fairly complex things. Consider Listing 7.7, which uses PHP''''s MySQL functions to retrieve a complete list of all the records in a user-specified table, convert this result set to XML, and format it into a HTML representation using PHP''''s SAX parser.

Listing 7.7 Reading a Database Table Using the DOM, and Formatting It into HTML with SAX

<?php 
// database parameters 
// get these via user input 
$host = "localhost"; 
$user = "joe"; 
$pass = "cool"; 
$db = "web"; 
$table = "bookmarks"; 
// segment 1 begins 
// query database for records 
$connection = mysql_connect
($host, $user, $pass) or die
 ("Unable to connect!"); 
mysql_select_db($db) or die
 ("Unable to select database!"); 
$query = "SELECT * FROM $table"; 
$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("table"); 
$root->set_attribute("name", $table); 
// create nodes for structure and data 
$structure = $root->new_child("structure", "); 
$data = $root->new_child("data", "); 
// let''''s get the table structure first 
// create elements for each field name, type and length 
$fields = mysql_list_fields($db, $table, $connection); 
for ($x=0; $x<mysql_num_fields($fields); $x++) 
{
$field = $structure->new_child("field", "); 
$name = mysql_field_name($fields, $x); 
$length = mysql_field_len($fields, $x); 
$type = mysql_field_type($fields, $x); 
$field->new_child("name", $name); 
$field->new_child("type", $type); 
$field->new_child("length", $length); 
} 
// move on to getting the raw data (records) 
// iterate through result set 
while($row = mysql_fetch_row($result)) 
{
$record = $data->new_child("record", "); 
foreach ($row as $field) 
{
$record->new_child("item", $field); 
} 
} 
// dump the tree as a string 
$xml_string = $doc->dumpmem(); 
} 
// close connection 
mysql_close($connection); 
// segment 1 ends 
// at this point, a complete representation
 of the table is stored in $xml_string 
// now proceed to format this into HTML with SAX 
// segment 2 begins 
// array to hold HTML
 markup for starting tags 
$startTagsArray = array(
''''TABLE'''' => ''''
<html><head></head><body
><table border="1"
 cellspacing="0"cellpadding="5">'''', 
''''STRUCTURE'''' => ''''<tr>'''', 
''''FIELD'''' => ''''
<td bgcolor="silver">
<font face="Arial" size="-1">'''', 
''''RECORD'''' => ''''<tr>'''', 
''''ITEM'''' => ''''<td>
<font face="Arial" size="-1">'''', 
''''NAME'''' => ''''<b>'''', 
''''TYPE'''' => ''''&nbsp;
&nbsp;<i>('''', 
''''LENGTH'''' => '''', '''' 
); 
// array to hold HTML markup for ending tags 
$endTagsArray = array(
''''TABLE'''' => ''''</body></html></table>'''', 
''''STRUCTURE'''' => ''''</tr>'''', 
''''FIELD'''' => ''''</font></td>'''', 
''''RECORD'''' => ''''</tr>'''', 
''''ITEM'''' => ''''&nbsp;</font></td>'''', 
''''NAME'''' => ''''</b>'''', 
''''TYPE'''' => '''''''', 
''''LENGTH'''' => '''')</i>'''' 
); 
// call this when a start tag is found 
function startElementHandler($parser, $name, $attributes) 
{
global $startTagsArray; 
if($startTagsArray[$name]) 
{
// look up array for this tag and print corresponding markup 
echo $startTagsArray[$name]; 
} 
} 
// call this when an end tag is found 
function endElementHandler($parser, $name) 
{
global $endTagsArray; 
if($endTagsArray[$name]) 
{
// look up array for this tag and print corresponding markup 
echo $endTagsArray[$name]; 
} 
} 
// call this when character data is found   
function characterDataHandler($parser, $data) 
{
echo $data; 
} 
// initialize parser 
$xml_parser = xml_parser_create(); 
// turn off whitespace processing 
xml_parser_set_option($xml_parser,XML
_OPTION_SKIP_WHITE, TRUE); 
// turn on case folding 
xml_parser_set_option($xml_parser, XML_
OPTION_CASE_FOLDING, TRUE); 
// set callback functions 
xml_set_element_handler($xml_parser, 
"startElementHandler", "endElementHandler"); 
xml_set_character_data_handler
($xml_parser, "characterDataHandler"); 
// parse XML 
if (!xml_parse($xml_parser, $xml_string, 4096)) 
{
$ec = xml_get_error_code($xml_parser); 
die("XML parser error
 (error code " . $ec . "): " . xml_error_string($ec) . 
"<br>Error occurred at line " 
. xml_get_current_line_number($xml_parser) . ", column " . 
xml_get_current_column_number($xml_parser) . ", byte offset " . 
xml_get_current_byte_index($xml_parser)); 
} 
// all done, clean up! 
xml_parser_free($xml_parser); 
// segment 2 ends 
?> 

Listing 7.7 can be divided into two main segments:

Retrieving database records and constructing an XML document from them

Converting the XML document into an HTML page

The first segment is concerned with the retrieval of the records from the table (using a catch-all SELECT * FROM table query), and with the dynamic generation of a DOM tree in memory using the DOM functions discussed previously. Once generated, this tree would be stored in the PHP variable $xml_string, and would look a lot like Listing 7.8.

Listing 7.8 An XML Representation of a MySQL Table

<?xml version="1.0"?> 
<table name="bookmarks"> 
<structure> 
<field> 
<name>category</name> 
<type>string</type> 
<length>255</length> 
</field> 
<field> 
<name>name</name> 
<type>string</type> 
<length>255</length> 
</field> 
<field> 
<name>url</name> 
<type>string</type> 
<length>255</length> 
</field> 
</structure> 
<data> 
<record> 
<item>News</item> 
<item>CNN.com</item> 
<item>http://www.cnn.com/</item> 
</record> 
<record> 
<item>News</item> 
<item>Slashdot</item> 
<item>http://www.slashdot.org/</item> 
</record> 
<record> 
<item>Shopping</item> 
<item>http://www.amazon.com/</item> 
</record> 
<record> 
<item>Technical Articles</item> 
<item>Melonfire</item> 
<item>http://www.melonfire.com/</item> 
</record> 
<record> 
<item>Shopping</item> 
<item>CDNow</item> 
<item>http://www.cdnow.com/</item> 
</record> 
</data> 
</table> 

After the MySQL result set has been converted into XML, it''''s fairly simple to parse it using SAX, and to replace the XML elements with corresponding HTML markup. This HTML markup is then sent to the browser, which displays it as a neatly formatted table (see Figure 7.2).

It''''s interesting to note that I could just as easily have accomplished this using XSLT instead of SAX. The process is fairly simple (much like Listing 7.6), and you should attempt to work it out for yourself. In case you get hung up on some of the more arcane aspects of XSLT syntax, Listing 7.9 has a stylesheet you can use to perform the transformation.

Listing 7.9 An XSLT Stylesheet to Format an XML Table Representation into HTML

<?xml version="1.0"?> 
<xsl:stylesheet version="1.0" xmlns:xsl=
"http://www.w3.org/1999/XSL/Transform"> 
<!-- set up page template --> 
<xsl:template match="/"> 
<html> 
<head> 
<basefont face="Arial" /> 
</head> 
<body> 
<table border="1" cellspacing="0" cellpadding="5"> 
<xsl:apply-templates select="//structure" /> 
<xsl:apply-templates select="//data" /> 
</table> 
</body> 
</html> 
</xsl:template> 
<!-- read structure data, set up first row of table --> 
<xsl:template match="//structure"> 
<tr> 
<!-- iterate through field list, print field information --> 
<xsl:for-each select="field"> 
<td bgcolor="silver"><font face="Arial" size="-1"
><b><xsl:value-of select="name" /
></b>  <i>(<xsl:value-of select="type" />,
 <xsl:value-of select="length" />)</i></font></
td> 
</xsl:for-each> 
</tr> 
</xsl:template> 
<!-- read records --> 
<xsl:template match="//data"> 
<!-- iterate through records --> 
<xsl:for-each select="record"> 
<tr> 
<!-- iterate through fields of each record --> 
xsl:for-each select="item"> 
<td><font face="Arial" 
size="-1"><xsl:value-of select="."/></
font>&#160;</td>   
</xsl:for-each> 
</tr> 
</xsl:for-each> 
</xsl:template> 
</xsl:stylesheet>