PHP - MySQL - XML tutorial - basics

The educational technology and digital learning wiki
Revision as of 19:13, 27 March 2013 by Daniel K. Schneider (talk | contribs) (→‎Server-side XSL transforms)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Introduction

Objectives

XML is often used to transport data between applications. In this tutorial (once it's fully done) we shall see how to:

  • Query a database with SQL, get the result in XML and render in HTML
  • Store simple XML data (e.g. tabular data) in MySQL (yet to be written....)
Prerequisites
Remark

We don't plan to write a tutorial on how to store complex XML data in an SQL database, because it's a too difficult subject for educational technology or computer application students. We rather suggest to use a XML database for that purpose.

Retrieving a database query as XML and rendering with XSLT

We will explain this with an example. You can either copy/paste code from below or get it from:

Remark: The PHP and the XSLT code work with any MySQL databases and tables.

Retrieving data as XML

Since SQL query results are available in a kind of 2-dimensional data-structure, generating code that has the following structure is not very difficult:

<result>
 <row>
   <col1>col 1 contents</col1> <col2>col 2 contents</col2> <col3>.....</col3>
 </row>
 <row>
 .....
 </row>
</result>

Here some example PHP code using the old php-mysql interface. Tested with the Mowes (PHP 5.3 /Apache 2.2.11 / MySQL 5.1.35)

To adapt to your local server, change the values in the configuration section of the PHP script. Make sure to test the SQL query with a MySQL administration tool.

<?php

/* Made by Daniel K. Schneider, TECFA Jan 2010. This is freeware
   Will connect to a MySQL database, execute an SQL statement and
   then return the result as valid XML. 
 */
error_reporting(E_ALL);

// ---------------------------  Configuration section

// Fill in according to your db settings (example is for a local and locked "play" WAMP server)
// Make sure to keep the ; and the ""
$host       = "localhost";
$user       = "root";
$pass       = "";
$database   = "exam";

// Replace by a query that matches your database
$SQL_query = "SELECT * FROM question,response_item WHERE question.id = response_item.id_question";

// Optional: add the name of XSLT file.
// $xslt_file = "mysql-result.xsl"; 

// -------------------------- no changes needed below

$DB_link = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $DB_link) or die ("Could not find or access the database.");
$result = mysql_query ($SQL_query, $DB_link) or die ("Data not found. Your SQL query didn't work... ");

// we produce XML
header("Content-type: text/xml");
$XML = "<?xml version=\"1.0\"?>\n";
if ($xslt_file) $XML .= "<?xml-stylesheet href=\"$xslt_file\" type=\"text/xsl\" ?>";

// root node
$XML .= "<result>\n";
// rows
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {    
  $XML .= "\t<row>\n"; 
  $i = 0;
  // cells
  foreach ($row as $cell) {
    // Escaping illegal characters - not tested actually ;)
    $cell = str_replace("&", "&amp;", $cell);
    $cell = str_replace("<", "&lt;", $cell);
    $cell = str_replace(">", "&gt;", $cell);
    $cell = str_replace("\"", "&quot;", $cell);
    $col_name = mysql_field_name($result,$i);
    // creates the "<tag>contents</tag>" representing the column
    $XML .= "\t\t<" . $col_name . ">" . $cell . "</" . $col_name . ">\n";
    $i++;
  }
  $XML .= "\t</row>\n"; 
 }
$XML .= "</result>\n";

// output the whole XML string
echo $XML;
?>

Typically, the above php script would produce data like this (node names correspond to SQL columns and will change if you use this script with your SELECT statement and your database):

<?xml version="1.0"?>
<?xml-stylesheet href="mysql-result.xsl" type="text/xsl" ?>
<result>
	<row>
		<sort_order>1</sort_order>
		<id>1</id>
		<description>What does SQL stand for ?</description>
		<id_survey>1</id_survey>
		<id>Structured Question Language</id>

		<response_item>0</response_item>
		<is_correct>Bad Answer</is_correct>
		<feedback_text>0</feedback_text>
		<score>1</score>
	</row>
	<row>
		<sort_order>1</sort_order>

		<id>2</id>
		<description>What does SQL stand for ?</description>
		<id_survey>1</id_survey>
		<id>Standard Query Language</id>
		<response_item>0</response_item>
		<is_correct>Bad answer</is_correct>

		<feedback_text>0</feedback_text>
		<score>1</score>
	</row>
   .......
</result>

Rendering with XSLT

The following example demonstrates how to add a client-side XSLT file. All you need to do is to specify an XSLT file name in the code above. E.g. unquote the following line:

// $xslt_file = "mysql-result.xsl"; 

The following XSLT code will work with the above PHP code and the kind of XML it produces. It should render any kind of output, i.e. adapt to your tables and SQL SELECT statement.

In order to produce the table headings we use the apply-templates' "mode" attribute to look at the first row and then to extract the node names for each cell. If you know what to expect from an SQL query you could replace this abstract "universal" code by custom rules.

Contents of file mysql-to-xml-raw.php:

<?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 Transitional//EN"/>

  <xsl:template match="result">
    <html> <head> <title> Query results </title> </head>
      <body bgcolor="#ffffff">

	<h1>Query results</h1>

	<table border="2" cellspacing="1" cellpadding="6">
	  <xsl:apply-templates select="row[1]" mode="headings"/>
	  <xsl:apply-templates select="row"/>
	</table>
	<hr />
        Generated by mysql-result.xsl
	(<a href="http://edutechwiki.unige.ch/en/PHP_-_MySQL_-_XML_tutorial_-_basics"> EduTechWiki.unige.ch</a>).
      </body>
    </html>
  </xsl:template>
  
  <xsl:template match="row" mode="headings">
    <tr> <xsl:apply-templates mode="headings"/> </tr>
  </xsl:template>

  <xsl:template match="*" mode="headings">
    <th> <xsl:value-of select="name(.)"/> </th>
  </xsl:template>

  <xsl:template match="row">
    <tr> <xsl:apply-templates/> </tr>
  </xsl:template>

  <xsl:template match="*">
    <td> <xsl:apply-templates/> </td>
  </xsl:template>
</xsl:stylesheet>

Result will be HTML code like this:

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
   <head>
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <title> Query results </title>
   </head>
   <body bgcolor="#ffffff">
      <h1>Query results</h1>
      <table border="2" cellspacing="1" cellpadding="6">
         <tr>
            <th>sort_order</th>
            <th>id</th>
            <th>description</th>
            <th>id_survey</th>
            <th>id</th>
            <th>response_item</th>
            <th>is_correct</th>
            <th>feedback_text</th>
            <th>score</th>
         </tr>
         <tr>
            <td>1</td>
            <td>1</td>
            <td>What does SQL stand for ?</td>
            <td>1</td>
            <td>Structured Question Language</td>
            <td>0</td>
            <td>Bad Answer</td>
            <td>0</td>
            <td>1</td>
         </tr>
         <tr>
            <td>1</td>
            <td>2</td>
            <td>What does SQL stand for ?</td>
            <td>1</td>
            <td>Standard Query Language</td>
            <td>0</td>
            <td>Bad answer</td>
            <td>0</td>
            <td>1</td>
         </tr>
.....
      </table>
      <hr>
      Generated by mysql-result.xsl
      (<a href="http://edutechwiki.unige.ch/en/PHP_-_MySQL_-_XML_tutorial_-_basics">
      EduTechWiki.unige.ch</a>).
      
   </body>
</html>

Further explorations

Server-side XSL transforms

Instead of having the client browser do the XSLT transform, you also can format the XML output on the serveur. There are at least two options

  • Parse the XML with PHP and generate HTML tags
  • Apply an XSLT stylesheet with PHP

The following code fragment shows how to apply an XSLT stylesheet to an XML File with server-side in php5. This is not exactly what we want.

<?php

# Made by DKS in 2005... should still work
error_reporting(E_ALL);

$xml_file = 'travaux.xml';
$xsl_file = 'travaux.xsl';

// load the xml file (and test first if it exists)
$dom_object = new DomDocument();
if (!file_exists($xml_file)) exit('Failed to open $xml_file');
$dom_object->load($xml_file);

// create dom object for the XSL stylesheet and configure the transformer
$xsl_obj = new DomDocument();
if (!file_exists($xsl_file)) exit('Failed to open $xsl_file');
$xsl_obj->load($xsl_file);
$proc = new XSLTProcessor;
$proc->importStyleSheet($xsl_obj); // attach the xsl rules

$html_fragment = $proc->transformToXML($dom_object);
print ($html_fragment); 

?>

Instead of reading an XML file we have to take an XML string as input. Code below is not tested but its intent must be ok, read the DOMDocument::loadXML entry of the PHP5 manual ....

<?php

error_reporting(E_ALL);
// Substitute the code from the database example above, 
// e.g. retrieve the data from mysql and generate an XML string
$XML = "<result> ..... </result>" // replace !!

$xsl_file = 'style.xsl';

// load the xml file (and test first if it exists)
$dom_object = new DomDocument();
$dom_object->loadXML($XML);  // will load a string into the DOM

// create dom object for the XSL stylesheet and configure the transformer
$xsl_obj = new DomDocument();
if (!file_exists($xsl_file)) exit('Failed to open $xsl_file');
$xsl_obj->load($xsl_file);
$proc = new XSLTProcessor;
$proc->importStyleSheet($xsl_obj); // attach the xsl rules

$html_fragment = $proc->transformToXML($dom_object);
print ($html_fragment); 

?>

Sticking XML into a MysQL table

... to be written ...

Using libraries

Since sticking XML into MySQL (in particular certain XML applications like RSS) and retrieving a result set as XML is a frequently encoutered problem, you also may consider various PHP libraries that do the job.

Examples (none tested !):

Links

Other tutorials

Manuals