Transforming Results of PostgreSQL Query to XML, using PHP DOM

Question

Hey everyone,

Given SQL as an input, I have to query a PostgreSQL database and return the results as XML. I have done this with the following code:

<?php

$link = "host=localhost dbname=company user=pgsql password=password";
$connect = pg_connect($link);

$query = "SELECT *  FROM customer";
$result = pg_query($connect, $query);

$doc = new DomDocument("1.0");

$root = $doc->createElement('data');
$root = $doc->appendChild($root);

while($row = pg_fetch_assoc($result)){
    $node = $doc->createElement('collection');
    $node = $root->appendChild($node);

    foreach($row as $fieldname => $fieldvalue){
       $node->appendChild($doc->createElement($fieldname, $fieldvalue));
    }
}

$doc->save("cust.xml");

?>

Eventually, I will be applying a CSS stylesheet directly to the XML document. I will specify style information for each 'collection' node. However, it is possible that there will be sub-collections of collections, and even sub-sub-collections and so on.

(So: Master, detail, sub-detail, sub-sub-detail, etc.)

The problem is, my code will only generate XML for Master, detail, sub-detail. How can I modify my code so that the XML generated will always "capture" all of the levels of the data?

Thanks...

---

Yes, I'm referring to a hierarchical relationship. For example, with my current code, I can see all of the attributes, of fields, of the "Customer" table. However, what if one of the customer attributes were 'Address', and the Customer had multiple addresses? This would need to appear as...

 <data> 
   <collection> 
     <fname>Joe</fname> 
     <lname>Smith</lname> 
     <address> 
       <address A> 123...</address A> 
       <address B> 234...</address B> 
   </collection>
 </data>

I'm trying to modify my code to "pick up" on the fact that address, although an attribute, has sub-attributes...

Answer 1

First, you may consider performing your xml mapping within Postgres itself using the available built-in functions. Two benefits of this are that your data abstraction functionality stays together and that Postgres is much better optimized to perform this task efficiently than php is. If you have to do any higher level modification of the xml, xslt should do the trick.

As for the problem you mention, I'm not sure if you're referring to the data in the table having a hierarchical relationship?