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

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

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

Vikram Vaswani

نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
افزودن یادداشت
افزودن یادداشت جدید















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();



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







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) .
"<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>



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.







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



/ 84