使用循环填充XML

I'm trying to figure out a way to populate an XML object more dynamically without having to constantly change the name of the nodes. I some code similar to this:

EDIT: Changed the data to be inline with actual db structure:

$query = ("Select order_id, created_on, updated_on, status from table orders;");

// Execute query
$result = mysql_query($query, $link) or die("Could not complete database query");

// Populate array
while(($resultArray[] = mysql_fetch_assoc($result)) || array_pop($resultArray)); 

  $doc = new DOMDocument();
  $doc->formatOutput = true; 

  $r = $doc->createElement( "data" );
  $doc->appendChild( $r ); 

  foreach( $resultArray as $record )
  {
  $b = $doc->createElement( "record" ); 

  $record1 = $doc->createElement( "Order" );
  $record1->appendChild(
  $doc->createTextNode( $record['order_id'] )
  );
  $b->appendChild( $record1 ); 

  $record2 = $doc->createElement( "Created" );
  $record2->appendChild(
  $doc->createTextNode( $record['created_on'] )
  );
  $b->appendChild( $record2 ); 

  $record3 = $doc->createElement( "Updated" );
  $record3->appendChild(
  $doc->createTextNode( $record['updated_on'] )
  );
  $b->appendChild( $record3 );

  $record4 = $doc->createElement( "Status" );
  $record4->appendChild(
  $doc->createTextNode( $record['status'] )
  );
  $b->appendChild( $record4 );

  $r->appendChild( $b );
  } 

  echo $doc->saveXML();

// Close connection
mysql_close($link);

This is fine, but if I want to add a 3rd column in the query I have to also add it to the loop. I'm sure there is a better way to actually do this.

Any advice?

Thanks.

Maybe this class I wrote will help you out...

<?PHP
class ArrayTo
{
    protected static function array2xml($xml, $arr, $elements)
    {
        foreach($arr as $key => $value)
        {
            $element = isset($elements[0]) ? $elements[0] : $key;
            if(is_array($value))
            {
                $xml->startElement($element);
                self::array2xml($xml, $value, array_slice($elements, 1));
                $xml->endElement();
                continue;
            }
            $xml->writeElement($element, $value);
            $elements = array_slice($elements, 1);
        }
    }

    public static function xml($arr, $root = 'root', $elements = Array(), $version = '1.0', $encoding = 'UTF-8')
    {
        $xml = new XMLWriter();
        $xml->openMemory();
        $xml->startDocument($version, $encoding);
        $xml->startElement($root);
        self::array2xml($xml, $arr, $elements);
        $xml->endElement();
        return $xml->outputMemory(true);
    }
}
?>

How to use it...

$data = array(
    "column1" => "im column 1",
    "column2" => "im column 2",
    "column3" => "im column 3",
    "column4" => "im column 4",
    "column5" => "im column 5",
);

echo ArrayTo::xml($data, 'table');

// output
<?xml version="1.0" encoding="UTF-8"?>
<table>
    <column1>im column 1</column1>
    <column2>im column 2</column2>
    <column3>im column 3</column3>
    <column4>im column 4</column4>
    <column5>im column 5</column5>
</table>

You can also use the third argument $elements to override the $key at the level with a custom element. For exmple...

echo ArrayTo::xml($data, 'table', array('COLUMN_1', 'COLUMN_2'));

// output
<?xml version="1.0" encoding="UTF-8"?>
<table>
    <COLUMN_1>im column 1</COLUMN_1>
    <COLUMN_2>im column 2</COLUMN_2>
    <column3>im column 3</column3>
    <column4>im column 4</column4>
    <column5>im column 5</column5>
</table>

I would do something like that:

$elemColMap = array(
    'Column1' => 'First_Column',
    'Column2' => 'Second_Column',
);

foreach( $resultArray as $record )
{
    $b = $doc->createElement( "record" ); 

    foreach($elemColMap as $xmlElem => $dbCol) {
        $record1 = $doc->createElement( $xmlElem );
        $record1->appendChild(
            $doc->createTextNode( $record[$dbCol] )
        );
        $b->appendChild( $record1 );        
    }

    $r->appendChild( $b );  
} 

You can do:

$column_num = 1;
foreach ( $record as $r_key => $r_value )
{
    if (!preg_match('/_Column$/', $r_key)
        continue;

    $xml_record = $doc->createElement( "Column".$column_num );
    $xml_record->appendChild(
            $doc->createTextNode( $r_value )
        );
    $b->appendChild( $xml_record ); 
    $column_num++;
}

Of course, you have not given us the exact DB structure, so I have assumed that the columns you want to add are ending with _Column (that's why there's that !preg_match('/_Column$/', $r_key) ). If you want all columns, just remove those 2 lines at the start of the loop.

EDIT: damn, I was 16 seconds too late :<