Using PHP 4s DOM XML Functions to Create XML Files from SQL Data

By Tony Marston
2005-04-04

Multiple occurrences of a single table

The following code will take the contents of $dbresult (any number of rows, each of which contains a series of name=value pairs) and write it to a variable as an XML string. This can subsequently be written to a disk file or transformed into an HTML document using the Sablotron XSLT processor which is built into PHP. For details on how to use this extension please refer to Using PHP 4's Sablotron extension to perform XSL Transformations

This first piece of code simply connects to the database and performs a query:

<?php 

if(!$dbconnect = mysql_connect('localhost', 'user', 'pass')) {

   echo "Connection failed to the host 'localhost'.";

   exit;

} // if

if (!mysql_select_db('test')) {

   echo "Cannot connect to database 'test'";

   exit;

} // if



$table_id = 'some_table';

$query = "SELECT * FROM $table_id";

$dbresult = mysql_query($query, $dbconnect);

Now that we have our data we transfer it to an XML document. We start by creating a new DOM document. The following command will set the XML version number to '1.0' and return the object reference for the new document:

// create a new XML document

$doc = domxml_new_doc('1.0');

The first element we create in the XML document is known as the root element. Each XML document must have 1, and only 1, root element. In this example I have called it 'root', but you can use whatever name you like (such as the name of the PHP script which is executing). Note that you have to create the element and insert it into the document with two functions.

// create root node

$root = $doc->create_element('root');

$root = $doc->append_child($root);

Now we are ready to start adding the data we have retrieved from the database. Note that I am returning each row as an associative array which provides me with a list of 'name=value' pairs. This makes all subsequent processing far easier.

// process one row at a time

while($row = mysql_fetch_assoc($dbresult)) {

The first task I must perform for each row is to add a new element to the XML document. Here I create a new element using the table name, then I insert it into the document as a child of the root element.

  // add node for each row

  $occ = $doc->create_element($table_id);

  $occ = $root->append_child($occ);

Now I loop through each column in the current row, and insert the fieldname and corresponding value. You will see how having an associative array makes life easy. I need not concern myself with how many columns have been returned from the database query, nor with the order in which they are presented, as every column in the array gets written out.

  // add a child node for each field

  foreach ($row as $fieldname => $fieldvalue) {

Note that here I create a new element for the field and then insert it as a child to the current database row, as identified in $occ.

    $child = $doc->create_element($fieldname);

    $child = $occ->append_child($child);

Now I must add the field value as a text node, then insert it as a child element to the current field node, as identified in $child.

    $value = $doc->create_text_node($fieldvalue);

    $value = $child->append_child($value);

These loops do not terminate until they have processed every column of every row which has been retrieved from the database.

  } // foreach

} // while

This next function returns the completed XML document as a string.

// get completed xml document

$xml_string = $doc->dump_mem(true);

Here I am simply outputting the results to the client browser, but I could just as easily perform some additional processing such as passing it to an XSLT processor for transformation into another document, such as an HTML document, using the contents of a separate XSL file.

echo $xml_string;

?>

The contents of the XML file produced with this code will look something like the following, starting with the XML declaration, with the version number, and immediately followed by the root node.

<?xml version="1.0"?>

<root>

For each database row there will be an element, as a child to the root node, which contains the table name Each row element will have a separate child element for each column within that row. Note that each column element contains a text node for its value, while the row element does not have a text node. This grouping will be repeated for each column within each row. After the last column, notice the closing tag for the current row element, after which the row/column group must be repeated for each additional row that was extracted from the database and transferred to the XML file.

  <table1>

    <column1>value1</column1>

    <column2>value2</column2>

    ............

    <columnX>valueX</columnX3>

  </table1>

  <table2>

    ............

  </table2>

The last line in an XML file is there to close the root node.

</root>

Note that each element within the XML document has an opening and a closing tag in the format <element>...</element>. This identifies the node name within the document tree. Everything between these two tags is a child node to that element. This child node may be a text node or another element.

You may sometimes see an element in an XML document shown as <element />. This signifies that the element is empty. When an element is empty, XML allows the opening and closing tags to be merged into a single self-closing tag.

Tutorial Pages:
» Intended Audience
» Prerequisites
» Multiple occurrences of a single table
» A One-to-Many relationship
» Adding optional attributes
» Using Multi-Byte Characters
» Conclusion
» References

Related Tutorials:
» Zend Framework Tutorial
» Port Scanning and Service Status Checking in PHP
» Web Database Access from Desktop Applications
» CubeCart 3.0 Installation and Configuration
» PHP Site Search Made Easy
» Installing and Configuring Drupal 6.1