From MySQL to HTML with PHP and XML

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. 

<?php include('sql2xml.php') ?>
<?php ob_start() ?>

<DOCUMENT>
    <?php 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 < 4
                   order by a.alb_id, s.sng_number', '2') ?>
</DOCUMENT>

<?php include('xsl.php') ?>

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

Nevermind
1 Breed
2 Come As You Are
3 Drain You
Band of Gypsys
1 Who Knows
2 Machine Gun
3 Changes


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 test2.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="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="html" doctype-public="-//W3C//DTD HTML 4.01//EN"
                doctype-system="http://www.w3.org/TR/html4/strict.dtd" />

    <xsl:template match="DOCUMENT"><!-- main template -->
        <html>
            <head>
                <title>From MySQL to HTML with PHP and XML (www.redips.net)</title>
            </head>
            <body>
                <table border="1">
                    <!-- album loop -->
                    <xsl:apply-templates select="ROW0"/>
                </table>
            </body>
        </html>
    </xsl:template>

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

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

</xsl:stylesheet>

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

yum install php-xml

After getting current buffer content (generated 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!

<?php

// 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;
$xml->loadXML($xml_data);
$xsl->load($xsl_file);

// load the xsl template
$xp->importStyleSheet($xsl);

// do XSL transformation and print result
print($xp->transformToXML($xml));

?>

If you want, you can find prepared examples in the redips1.tar.gz (3KB) package. It contains all files from this and previous post.

4 thoughts on “From MySQL to HTML with PHP and XML”

  1. hi Darko,
    great post!

    having worked on xml and xsl, i could not figure how to process data with xml/xsl with from a database without writing it to a file! i got a very useful answer here.

    and then i was looking only for this line : $xml->loadXML($xml_data);

    I haven’t used “yum utility”… my php version is 5.2.10

    funny thing… i read this post before your’s http://wonko.com/post/using_xslt_as_a_php_template_engine

    thank’s (hvala)
    nicolas

  2. Nicolas, all I can say that described procedure MySQL->XML->HTML really works. I have it in production on a public site for a past few years and it is very stable. APC “saves” database from a constant querying … Anyway, thank you for commenting on my site. Greetings from Croatia! ;)

Leave a Comment