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

By Tony Marston
2005-04-04

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. In the following code 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 parent/outer table

$outer = $doc->create_element($outer_table);

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

We must not forget to add each column value as a child element to the $outer node.



// take only one row from parent/outer table

$row = mysql_fetch_assoc($resouter);



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

Finally, get the completed XML document and send it to the client's browser.

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

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

    <columnX>valueX</columnX>

    <child_table>

      <column1>value1</column1>

      <column2>value2</column2>

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

      <columnX>valueX</columnX>

    </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>.

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