Question with XML construction using PHP

Question

Hey everyone,

Currently, I am constructing an XML document in PHP that will yield the following:

<root>
  <collection>
    <region>1</region>
    <primary>John</primary>
  <collection>
  <collection>
    <region>1</region>
    <primary>Jane</primary>
  <collection>
  <collection>
    <region>2</region>
    <primary>Jill</primary>
  <collection>
<root>

However, I am looking to get the following:

<root>
  <collection>
    <region>1</region>
      <primary>John</primary>
      <primary>Jane</primary>
  <collection>
  <collection>
    <region>2</region>
      <primary>Jill</primary>
  <collection>
<root>

To get the first XML doc, I am using the following PHP code:

$query = mysql_query("SELECT * FROM eventcal WHERE eventDate = '$date' ORDER BY region");

$doc = new DomDocument("1.0");

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

if (@mysql_num_rows($query)) {
 while ($row=@mysql_fetch_assoc($query)) { 

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

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

Is it possible for me to modify that PHP to have "primary" as a child of "region"?

Thanks!

---

Sorry guys, I meant primary as a sibling. You're right, the collection tag would become superfluous. Based on your comments, I think that the structure should be changed to:

 <root>
   <collection>
     <region ID = "1">
       <primary>John</primary>
       <primary>Jane</primary>
     </region>
     <region ID = "2">
       <primary>Jill</primary>
   </collection>
 <root>

My problem then is, how can isolate region as a parent from the MySQL resource that is returned from the query?

Thanks.

Answer 1

I think what you want is this:

<?php

$query = mysql_query("SELECT * FROM eventcal WHERE eventDate = '$date' ORDER BY region");

$doc = new DomDocument("1.0");

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

$currentRegionId = -1;
$currentRegionNode = null;

if (@mysql_num_rows($query)) {
        while ($row=@mysql_fetch_assoc($query)) {

     if ($row['region'] != $currentRegionId)
     {
      $currentRegionId = $row['region'];

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

      $currentRegionNode = $doc->createElement('region');
      $currentRegionNode->setAttribute('id', $row['region']);
      $node->appendChild($currentRegionNode);
     }

        foreach($row as $fieldname => $fieldvalue){
      if ($fieldname != 'region')
       $currentRegionNode->appendChild($doc->createElement($fieldname, $fieldvalue));
        }
    }
}

?>

Answer 2

You first need to understand the XML itself better. You want region to be a complex type containing the primary elements hence you probably want to introduce a new element or attribute to hold the regions ID. For example you may want XML to look look this:-

<root>
  <collection>
    <region ID="1">
      <primary>John</primary>
      <primary>Jane</primary>
    </region>
  </collection>
  <collection>
    <region ID="2">
       <primary>Jill</primary>
    </region>
  </collection>
</root>

Note how currently the collection element has become superflous since it only ever holds a single region element. Its important the you continue to evolve your actual XML structure before you write anymore code.