PHP - MySQL - XML tutorial - basics

Draft

This article or section is currently under construction

In principle, someone is working on it and there should be a better version in a not so distant future.
If you want to modify this page, please discuss it with the person working on it (see the "history")

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

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

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)

<?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 = ""; 

// -------------------------- 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) $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;
?>

Rendering with XSLT

• See the XSLT Tutorial - Basics for XSLT.

Here we show an example on how to add a client-side XSLT file. All you need to do is to specify an XSLT file name in the code above.

The following XSLT code will work with the above PHP code and the kind of XML it produces. In order to produce the table headings we use the "mode" attribute to look at the first row and 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.

<?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>

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 (in particular certain applications like RSS into MySQL and retrieving a result set as XML is a frequently encoutered problem, you also may consider various PHP libraries that do the job.

Example (none tested):

• XML 2 PHP and PHP 2 XML
• DbToXML