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.
<? include('sql2xml.php') ?>
<? ob_start() ?>
<DOCUMENT>
<? 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') ?>
</DOCUMENT>
<? 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:
| Nevermind | |
| 1 | Breed |
| 2 | Come As You Are |
| Band of Gypsys | |
| 1 | Who Knows |
| 2 | Machine Gun |
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="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>
<body>
<table border="1"><xsl:apply-templates select="ROW0"/></table><!-- album loop -->
</body>
</html>
</xsl:template>
<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>
<xsl:template match="ROW1"><!-- song template -->
<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 test1.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 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;
$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. I hope my scripts will be useful for you.
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
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!