PHP - MySQL - XML tutorial - basics

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

• A little bit of PHP
• Some SQL (see the SQL and MySQL tutorial)
• XML (basic concepts)
• XSLT (for some parts, see XSLT Tutorial - Basics

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:

• http://tecfa.unige.ch/guides/xml/examples/mysql-php-xml/

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

• See the XSLT Tutorial - Basics for 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

Other tutorials

• Output mySQL data as XML with PHP by Jubba, Kirupa.com. (easy)
• Using XML in MySQL 5.1 and 6.0 (shows for example how to create XML result sets with a simple MySQL or SELECT commands).

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 !):

• XML 2 PHP and PHP 2 XML
• DbToXML