Exporting Database Records To XML

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

  1. Connect to the database.

  2. Retrieve a result set.

  3. Iterate through this result set, and create XML structures corresponding to the data retrieved.

  4. 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:

  1. 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()); 
  2. 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"); 
  3. 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"); 
  4. 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);  } 
  5. After the document has been completely generated, the dumpmem() object method is used to dump the XML tree as a string.

     echo $doc->dumpmem(); 

Cheat Sheet

This chapter makes extensive use of the following MySQL functions:

  • mysql_connect() Opens a connection to a MySQL database

  • mysql_select_db() Selects a particular MySQL database for use

  • mysql_query() Executes a query on the selected MySQL database

  • mysql_fetch_row() Fetches a single row of the MySQL result set

  • mysql_close() Closes a connection to a MySQL database

  • mysql_error() If an error occurs during query execution, returns the error string

More information on these functions, together with usage examples, can be obtained from your copy of the PHP manual or from the New Riders book PHP Functions Essential Reference , by Zak Greant, Graeme Merrall, Torben Wilson, and Brett Michlitsch (New Riders, 2001, ISBN: 0-7357-0970-X).

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> 

Backtrack

Still not too clear about how Listing 7.1 works? Flip back to Chapter 3, "PHP and the Document Object Model (DOM)" (in particular, refer to Listing 3.15, which bears more than a passing resemblance to Listing 7.1), refresh your memory about how PHP's DOM extension works, and 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 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. " . graphics/ccc.gif 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.

Emerging from a Cocoon

After 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/.

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. " graphics/ccc.gif .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 " . graphics/ccc.gif 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.

graphics/07fig01.gif

Party Pooper

Wondering what XSLT is, and why it's decided to crash this particular party? Chapter 4, "PHP and Extensible Stylesheet Language Transformations (XSLT)," has the skinny.

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) . graphics/ccc.gif "<br>Error occurred at line " . xml_get_current_line_number($xml_parser) . ", column " . graphics/ccc.gif xml_get_current_column_number($xml_parser) . ", byte offset " . graphics/ccc.gif 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> 

Taking the Scenic Route

You may be wondering whether the long, convoluted process outlined in Listing 7.7 was even necessary. Strictly speaking, it wasn'tI could have achieved the same effect with PHP's MySQL functions alone, completely bypassing the DOM and SAX parsers (and obtaining a substantial performance benefit as a result). XML was added to the equation primarily for illustrative purposes, to demonstrate yet another of the myriad uses to which PHP's DOM and SAX extensions can be put when working with XML-based applications.

Note that the approach outlined in Listing 7.7 is not recommended for a production environment, simply because of the performance degradation likely to result from using it. When working with tables containing thousands of records, the process of retrieving data, converting it to XML, parsing the XML, and formatting it into HTML would inevitably be slower than the shorter, simpler process of directly converting the result set into HTML using PHP's native functions and data structures.

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.

graphics/07fig02.gif

Revisiting SAX

SAX, the Simple API for XML, provides an efficient, event-driven approach to parsing an XML document. If you're not familiar with how it works, or with the SAX functions used in Listings 7.7 and 7.11, drop by Chapter 2, "PHP and the Simple API for XML (SAX)," which should bring you up to speed.

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" / graphics/ccc.gif ></b>  <i>(<xsl:value-of select="type" />, <xsl:value-of select="length" />)</i></font></ graphics/ccc.gif 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="."/></ graphics/ccc.gif font>&#160;</td>                      </xsl:for-each>        </tr>        </xsl:for-each>  </xsl:template>  </xsl:stylesheet> 
I l @ ve RuBoard


XML and PHP
XML and PHP
ISBN: 0735712271
EAN: 2147483647
Year: 2002
Pages: 84

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net