Wednesday, May 25, 2011

10:46 AM

In my post From MySQL to XML with PHP, I described how to generate XML from the MySQL database. Instead of printing out XML, generated XML can be buffered and transformed with XSL to the HTML. Maybe it sounds complicated for a simple process of displaying MySQL data on the WEB page. But if you set a WEB architecture this way, you will have a separated presentation layer from the database and business logic.

Here is example of final PHP source. You can see how it looks simple and clean. If you compare this code with PHP source from the previous post, you can notice two lines more: ob_start and bottom xsl.php include. ob_start starts output buffering while xsl.php uses this buffer and performs XSL transformation.

<? include('sql2xml.php') ?>
<? ob_start() ?>
    <? sql2xml('select a.alb_id, a.alb_name,
                                         s.sng_number, s.sng_name
                            from album a, song s
                            where a.alb_id = s.alb_id and   s.sng_number < 3
                            order by a.alb_id, s.sng_number', '2') ?>
<? include('xsl.php') ?>

Save file as test1.php and if you run it from the command line php test1.php, you should get the following HTML table:

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "">
    <table border="1">
      <tr><td colspan="2">Nevermind</td></tr>
      <tr><td>2</td><td>Come As You Are</td></tr>
      <tr><td colspan="2">Band of Gypsys</td></tr>
      <tr><td>1</td><td>Who Knows</td></tr>
      <tr><td>2</td><td>Machine Gun</td></tr>

XSL transformation needs XML and XSL file. If you want to know how XML looks, please open From MySQL to XML with PHP post. Here is the XSL file that is used to transform generated XML. Please name it test1.xsl. It's important to have the same name as PHP file (except suffix), otherwise XSL file will not be found.

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="">
<xsl:output method="html" doctype-public="-//W3C//DTD HTML 4.01//EN"
            doctype-system="" />

  <xsl:template match="DOCUMENT"><!-- main template -->
            <table border="1"><xsl:apply-templates select="ROW0"/></table><!-- album loop -->

  <xsl:template match="ROW0"><!-- album template -->
      <tr><td colspan="2"><xsl:value-of select="ALB_NAME"/></td></tr>
      <xsl:apply-templates select="ROW1"/><!-- song loop -->

  <xsl:template match="ROW1"><!-- song template -->
          <td><xsl:value-of select="SNG_NUMBER"/></td>
          <td><xsl:value-of select="SNG_NAME"/></td>

Before you run php test1.php, make sure you have installed php-xml package or you will get XsltProcessor not founderror. To install php-xml package with the yum utility, run the following command:

yum install php-xml

After getting current buffer contents (XML), and preparing XSL file name, script will begin the XSL transformation and generate the final HTML. Before printing, HTML can be stored to the cache - APC. Next time, instead of database, HTML can be fetched from the cache and this is very fast!

// get current buffer contents and delete current output buffer
$xml_data = ob_get_clean(); 

// define xsl file name from the script itself
$_name    = explode('.', basename($_SERVER['SCRIPT_NAME']));
$xsl_file = current($_name) . '.xsl';

// create XSLT processor
$xp = new XsltProcessor();
// load the xml document and the xsl template
$xml = new DomDocument;
$xsl = new DomDocument;

// load the xsl template

// do XSL transformation and print result