Formatting Dynamically Generated XML with SAX
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 companion web site of this article's related book, (http://www.xmlphp.com).
After you understand the basics, it's possible to apply simple XML programming techniques to do some fairly complex things. Consider Listing 1, 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 1—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 1 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 2.
Listing 2—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 1 was even necessary. Strictly speaking, it wasn't—I 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 1 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 1).
)
Figure 1 The result of formatting a dynamically generated, XML-encoded
database schema into an HTML table with SAX
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 Listing 1, refer to Chapter 2, "PHP and the Simple API for XML (SAX)," in the book, XML and PHP (New Riders, 2002), for detailed information, or take a look at the article "Using PHP With XML" (http://www.melonfire.com/community/columns/trog/article.php?id=71) on the Melonfire web site (http://www.melonfire.com/).
It's interesting to note that I could just as easily have accomplished this using XSLT instead of SAX. The process is fairly simple, 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 3 has a stylesheet you can use to perform the transformation.
Listing 3 - 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>
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.