Prerequisites
The sample code requires that you have the DOM XML extension available in your PHP installation. It is also assumed that you know what an XML file is and what it can be used for.

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.

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 list which provides me with an array of ?name=value? pairs. This makes all subsequent processing far easier.

// process each row from the sql query
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 list 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 everything 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.

  <some_table>
    <column1>value1</column1>
    <column2>value2</column2>
    ........................
    <columnX>valueX</columnX>
  </some_table>
  <some_table>
    ........................
  </some_table>
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 tag.

A One-to-Many relationship
In the following example the XML string will contain data from two tables arranged in a One-to-Many (or parent-to-child, or outer-to-inner) relationship. Two query results are produced: $resouter for the parent table, and $resinner for the child table. I shall only comment this code where there are differences.

<?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
Here, for example, are separate database queries for each of the two tables:

$outer_table = 'parent_table';
$query = "SELECT * FROM $outer_table WHERE column='value'";
$resouter = mysql_query($query, $dbconnect);

$inner_table = 'child_table';
$query = "SELECT * FROM $inner_table WHERE column='value'";
$resinner = mysql_query($query, $dbconnect);
Here we create a new Dom document and add the root node:

// create a new XML document
$doc = domxml_new_doc('1.0');

// add root node
$root = $doc->create_element('root');
$root = $doc->append_child($root);
Here we add a node for the single row obtained from the parent table:

// add node for outer/parent/one record
$outer = $doc->create_element($outer_table);
$outer = $root->append_child($outer);

// take only one row from outer/parent/one table
$row = mysql_fetch_assoc($resouter);
We must not forget to add each column value as a child element to the $outer node.

// add a child node for each parent field
foreach ($row as $fieldname => $fieldvalue) {
  $child = $doc->create_element($fieldname);
  $child = $outer->append_child($child);
  $value = $doc->create_text_node($fieldvalue);
  $value = $child->append_child($value);
} // foreach
Here we add a node for each row obtained from the child table. Note that each of these rows is inserted as a child node to the $outer node, not the $root node. Each $inner node will have its column values inserted as its children.

// process all rows of the inner/many/child table
while ($row = mysql_fetch_assoc($resinner)) {
  // add node for each record
  $inner = $doc->create_element($inner_table);
  $inner = $outer->append_child($inner);
  // add a child node for each field
  foreach ($row as $fieldname => $fieldvalue) {
    $child = $doc->create_element($fieldname);
    $child = $inner->append_child($child);
    $value = $doc->create_text_node($fieldvalue);
    $value = $child->append_child($value);
  } // foreach
} // while

// get completed xml document
$xml_string = $doc->dump_mem(true);
echo $xml_string;
?>
The above code will produce an XML file with the following structure:

<?xml version="1.0"?>
<root>
  <parent_table>
    <column1>value1</column1>
    <column2>value2</column2>
    <column3>value3</column3>
    <child_table>
      <column1>value1</column1>
      <column2>value2</column2>
      <column3>value3</column3>
    </child_table>
    <child_table>
      ...............
    </child_table>
  </parent_table>
</root>
This has the structure <root> to <parent_table> to <child_table>. The <parent_table> has child nodes which are its column values as well as multiple occurrences of <child_table>

Adding optional attributes
It may sometimes be necessary to include additional information for an element with the XML data, and this can be done in the form of attributes. An attribute has a name and a value, and any number of attributes can be added to an element. This must be done using the '->set_attribute' method immediately after the '->append_child' method and before any '->create_text_node' method, as in the following code snippet:

$child = $doc->create_element($fieldname);
$child = $outer->append_child($child);
// set any attributes here
$child->set_attribute('attr1', 'attrval1');
$child->set_attribute('attr2', 'attrval2');
// now continue with the field value
$value = $doc->create_text_node($fieldvalue);
$value = $child->append_child($value);
These attribute values will then appear within the element's start tag, as follows:

<?xml version="1.0"?>
<root>
  <some_table>
    <column1 attr1="attrval1" attr2="attrval2">value1</column1>
    <column2 attr1="attrval1" attr2="attrval2">value2</column2>
    <column3 attr1="attrval1" attr2="attrval2">value3</column3>
  </some_table>
</root>
Note that you can insert attributes for row elements as well as column elements.

In my own application I use attribute values to specify the size of each column, so that it does not have to be hard-coded within the XSL file. For multi-line columns I pass values for both 'rows' and 'cols'.

I also use attributes to include any error messages. All error messages get inserted to an array called $errors where the key is the fieldname and the value is the message. The code to insert the error message into the XML document as an attribute of the field which generated the error is as simple as this:

if (isset($errors[$fieldname])) {
   $child->set_attribute("error", $errors[$fieldname]);
} // if
Conclusion
By using this method I have been able to develop a generic mechanism for creating XML files based on the relationship of the database tables concerned. All I need do is specify the table names(s) and the selection criteria, and whatever comes out of the database will be transferred to an XML file for subsequent transformation into HTML using an XSL file.





http://www.zend.com/zend/tut/tutorial-DOM-XML.php