Generating the XML from data stored in a database

It may be that the data we want is stored in a database. What we could do is as follows:

extract the required data from the database;
generate some XML from this data;
transform this XML using an XSL document;
display the resulting HTML.

For example, suppose we want to offer a WWW page where the visitor can look at a database containing some items displaying those items that have a cost less than some price. We will provide a WWW form for the visitor to type in the price.

Suppose we also want to offer the visitor a choice on how to display the data. We could provide radio buttons on the WWW form to get the visitor to choose between different styles of layout.

http://webster.dur.ac.uk/barry.cornelius/myphp/xslt/prices.htm.

Here is a WWW form that achieves this:

<HTML>
   <BODY>
      <FORM METHOD="POST" ACTION="prices.php">
         What is your choice of price?<BR>
         <INPUT TYPE="text" NAME="somevalue">
         <BR>
         <INPUT TYPE="radio" NAME="layout" VALUE="plain"> plain
         <BR>
         <INPUT TYPE="radio" NAME="layout" VALUE="table"> table
         <BR>
         <INPUT TYPE="submit" VALUE="Submit price">
         <INPUT TYPE="reset">
      </FORM>
   </BODY>
</HTML>

When the visitor to this form clicks on the Submit price button, the prices.php script will be executed with two variables already set: the $somevalue variable will be initialised to the value that was typed in the form's textbox, and the $layout variable will be initialised to either plain or table.

We can use the value of the $somevalue variable in an SQL query (that produces a resultset):

mysql_connect("mysql.dur.ac.uk", "", "");
$SQLQuery = "SELECT * FROM consum WHERE price < $somevalue";
$result = mysql_db_query("Pdcl0bjc_prices", $SQLQuery);
$numrows = mysql_numrows($result);
for ($rownum = 0; $rownum<$numrows; $rownum++) {
   echo mysql_result($result,$rownum,"ID");
   echo mysql_result($result,$rownum,"goods");
   echo mysql_result($result,$rownum,"price");
   echo "<BR>\n";
}

Each iteration of the above for loop outputs three values. Instead of using the for loop to output these values, we could get it to produce XML-decorated forms of these values, e.g. to generate something like:

<product>
<ID>42</ID>
<goods>3.5" floppy disc</goods>
<price>0.35</price>
</product>

And we can get it to append these characters to the end of a string (being stored in a variable called $xml). This is illustrated by the prices.php script:

<%
   mysql_connect("mysql.dur.ac.uk", "", "");
   $SQLQuery = "SELECT * FROM consum WHERE price < $somevalue";
   $result = mysql_db_query("Pdcl0bjc_prices", $SQLQuery);
   $numrows = mysql_numrows($result);
   if ( $numrows == 0) {
      die("<p>There are no consumables with a price < $somevalue</p>");
   }
   $xml = "<?xml version=\"1.0\"?><consumables>";
   for ($rownum = 0; $rownum<$numrows; $rownum++) {
      $xml .= "<product>";
      $xml .= "<ID>".mysql_result($result,$rownum,"ID")."</ID>";
      $xml .= "<goods>".mysql_result($result,$rownum,"goods")."</goods>";
      $xml .= "<price>".mysql_result($result,$rownum,"price")."</price>";
      $xml .= "</product>";
   }
   $xml .= "</consumables>";
   $arguments = array("/_xml" => $xml);
   $xsltproc = xslt_create();
   $html = xslt_process($xsltproc, "arg:/_xml", $layout.".xsl", NULL, $arguments); 
   if (!$html) {
      die("XSLT processing error:" . xslt_error($xsltproc));
   }
   xslt_free($xsltproc);
   echo $html;
%>

The above script arranges for the value of $xml to be passed to a call of xslt_process. In this call, the third argument is either the value "plain.xsl" or the value "table.xsl".

The file plain.xsl could contain:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:output method="html"/>
   <xsl:template match="consumables">
      <html>
      <body>
      <pre>
         <xsl:apply-templates/>
      </pre>
      </body>
      </html>
   </xsl:template>
   <xsl:template match="product">
      <xsl:value-of select="ID"/>
      <xsl:value-of select="goods"/>
      <xsl:value-of select="price"/>
      <br/>
   </xsl:template>
</xsl:stylesheet>

and the file table.xsl could contain:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:output method="html"/>
   <xsl:template match="consumables">
      <html>
      <body>
      <table>
      <tr bgcolor="yellow">
      <td>ID</td>
      <td>goods</td>
      <td>price</td>
      </tr>
         <xsl:apply-templates/>
      </table>
      </body>
      </html>
   </xsl:template>
   <xsl:template match="product">
      <tr>
      <td><xsl:value-of select="ID"/></td>
      <td><xsl:value-of select="goods"/></td>
      <td><xsl:value-of select="price"/></td>
      </tr>
   </xsl:template>
</xsl:stylesheet>