PHP - MySQL - XML tutorial - basics: Difference between revisions
mNo edit summary |
mNo edit summary |
||
Line 7: | Line 7: | ||
; Objectives | ; Objectives | ||
[[XML]] is often used to transport data between applications. In this tutorial (once it's fully done) we shall see how to: | [[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 | * Store simple XML data (e.g. tabular data) in MySQL | ||
; Prerequisites | ; Prerequisites | ||
Line 17: | Line 17: | ||
;Remark: | ;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 | 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. | ||
</div> | </div> | ||
== | == 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: | |||
<source lang="xml"> | |||
<result> | |||
<row> | |||
<col1>col 1 contents</col1> <col2>col 2 contents</col2> <col3>.....</col3> | |||
</row> | |||
<row> | |||
..... | |||
</row> | |||
</result> | |||
</source> | |||
Here some example PHP code using the old php-mysql interface. Tested with the [[Modular Webserver System|Mowes]] (PHP 5.3 /Apache 2.2.11 / MySQL 5.1.35) | |||
<source lang="php"> | |||
<?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; | |||
?> | |||
</source> | |||
=== 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 uncomment the 2 lines in the code above: | |||
<source lang="php"> | |||
</source> | |||
== Further explorations == | == Further explorations == |
Revision as of 21:13, 8 February 2010
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 uncomment the 2 lines 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):