I l @ ve RuBoard |
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 DatabaseI'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:
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:
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>
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 XSLTThe 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>  </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. Figure 7.1. The result of transforming a dynamically generated XML document with XSLT.
Formatting Dynamically Generated XML With SAXAfter 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' => ' <i>(', 'LENGTH' => ', ' ); // array to hold HTML markup for ending tags $endTagsArray = array( 'TABLE' => '</body></html></table>', 'STRUCTURE' => '</tr>', 'FIELD' => '</font></td>', 'RECORD' => '</tr>', 'ITEM' => ' </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:
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). Figure 7.2. The result of formatting a dynamically generated, XML-encoded database schema into an HTML table with SAX.
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> </td> </xsl:for-each> </tr> </xsl:for-each> </xsl:template> </xsl:stylesheet> |
I l @ ve RuBoard |