PHP - MySQL - XML tutorial - basics
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("&", "&", $cell);
$cell = str_replace("<", "<", $cell);
$cell = str_replace(">", ">", $cell);
$cell = str_replace("\"", """, $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.
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):