The example you're about to see extracts data from a real database that I created to manage the statistics for my recreational hockey team, Music City Mafia. The database is a MySQL database that stores statistics for both games and players. In this example, you're only concerned with game data, which is stored in a database table called games. To access the data and initiate a SQL query, I'm using PHP, which is an open source scripting language used to create dynamic web pages. PHP has very good integration with MySQL, and is a great option for dynamic web page development that involves MySQL databases and XML.
PHP is a recursive acronym that stands for PHP Hypertext Processer. To learn more about PHP, visit the official PHP web site at http://www.php.net/.
Although the code you're about to see is written in PHP, you don't have to understand the PHP language in order to get the gist of what's going on. The key things to pay attention to are the SQL query being made on the database and the generation of the XML code. PHP is used to carry out these tasks but the code isn't too terribly difficult to decipher.
Listing 19.1 contains the code for the mcm_schedule.php sample web page that uses PHP to dynamically generate an XML file based upon a MySQL database query.
Listing 19.1. The Hockey Game Schedule PHP Example Document
<?php
// Connect to the database
$mcm_db = mysql_connect("localhost", "admin", "password");
mysql_select_db("mcm_hockey", $mcm_db);
// Issue the query
$mcm_query = sprintf("SELECT date, time, opponent, location, type, outcome, gf, ga, overtime FROM games WHERE season=\"%s\" ORDER BY date", $season);
$mcm_result = mysql_query($mcm_query, $mcm_db);
// Format the query results as XML
if (mysql_num_rows($mcm_result) > 0) {
// Assemble the XML code
$xml ="<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\r\n";
$xml.="<games>\r\n";
while (list($date, $time, $opponent, $location, $type, $outcome,
$gf, $ga, $overtime) = mysql_fetch_array($mcm_result)) {
$formatted_date = date("F j, Y", strtotime($date));
$formatted_time = date("g:ia", strtotime($time));
$xml.= sprintf(" <game date=\"%s\" time=\"%s\">\r\n",
$formatted_date, $formatted_time);
$xml.= sprintf(" <opponent>%s</opponent>\r\n", $opponent);
$xml.= sprintf(" <location>%s</location>\r\n", $location);
$xml.= sprintf(" <score outcome=\"%s\" overtime=\"%s\">
%s - %s</score>\r\n", $outcome, $overtime, $gf, $ga);
$xml.= " </game>\r\n";
}
$xml.="</games>";
// Write the XML code to the file mcm_results.xml
$file= fopen("mcm_results.xml", "w");
fwrite($file, $xml);
fclose($file);
echo "The XML document has been written - <a href=\"mcm_results.xml\">
view the XML code.</a>";
} else {
echo "Sorry, no matching records found.";
}
// Close the database
mysql_close($mcm_db);
?>
The first few lines of the page establish a database connection and open the Music City Mafia hockey database. A SQL query is then constructed based upon a parameter ($season) that is passed into the page via the URL. The point of this parameter is to allow you to limit the XML file to a particular season of data. For example, to generate an XML file with only the game data for the 2005 Summer hockey season, the following URL is used: http://www.musiccitymafia.com/mcm_schedule.php?season=Summer%202005.
The %20 near the end of URL is just a separator to provide a space between the word Summer and the word 2005. The result of this URL is that the mcm_schedule.php web page assigns the value Summer 2005 to the variable $season, which can then be used throughout the PHP code. And, in fact, it is when the SQL query is issued in lines 7 through 9 of the listing. More specifically, the date, time, opponent, location, type, outcome, goals for, goals against, and overtime database fields are selected from the games table but only for the Summer 2005 season. The result of this query is stored in the $mcm_result variable (line 10).
By the Way
In PHP programming, all variable names are preceded by a dollar sign ($).
The next big chunk of code goes through the results of the SQL query one record at a time, formatting the data into XML code. Notice that the XML processor directive is first generated (line 15), followed by a root tag, <games> (line 16). Each piece of pertinent game data is then further formatted into XML code in lines 17 through 28. The document is wrapped up with a closing </games> tag in line 29.
The last important step in the PHP code is writing the XML data to a file. The file is named mcm_results.xml, and the XML data is written to it with just a few lines of code (lines 32 to 34). A simple line of HTML code is then written to the browser so that you can access the XML file. More specifically, a link is generated that allows you to click and view the XML document (lines 36 and 37).
The remaining code in the PHP web page prints an error message if no records were found for the database query (line 39), and then closes the database (line 42).
Figure 19.4 shows the finished PHP document as viewed in Internet Explorer.
Figure 19.4. The hockey game schedule PHP document generates an XML file from a SQL database query, and then provides a link to the file.
Notice in the URL in the figure that the Summer 2005 season was specified, which limits the database query results to only those games in the Summer 2005 season. If you click the link on the page, the XML file is opened in the browser, as shown in Figure 19.5.
Figure 19.5. The resulting XML document contains cleanly formatted XML code that was dynamically generated from a database query.
This figure reveals how the dynamically generated XML document contains structured data that originated from a purely tabular database. You can now run with this XML code and manipulate it just as you would any other XML document. You can transform it using XSLT, style it using CSS or XSL-FO, or automatically process it using some kind of specialized XML tool.
Although I've focused on the open source MySQL database throughout this lesson, many commercial databases also include support for XML. Additionally, there are native XML databases, also called NXDs, that allow you to work entirely in XML code with database queries always resulting in pure XML code. To learn more about XML database products, visit http://www.rpbourret.com/xml/XMLDatabaseProds.htm.