I need to create an XML file (using DOM) from a SQL database. The resulting XML file will be used to create a navigational menu for the site. Any help is REALLY appreciated. The explanation below is just a skeleton I know I haven't added anything to do with the target URL just yet....one step at a time :-).....So here goes:
I have a MySQL table with the following structure:
id heading subheading subsubheading
0 heading 1 subheading 1 subsubheading 1
1 heading 1 subheading 1 subsubheading 2
2 heading 1 subheading 2
3 heading 1 subheading 2 subsubheading 1
4 heading 2 subheading 1
5 heading 2 subheading 2
Which I will use to create a menu hierarchy with the following structure:
- heading 1
--- subheading 1
--------- subsubheading 1
--------- subsubheading 2
--- subheading 2
--------- subsubheading 1
- heading 2
--- subheading 1
--- subheading 2
However, rather than doing this just using PHP I must (one of the system requirements) create an XML document (using DOM). I will then use XSLT to display the resulting XML document in the browser as a navigational menu.
The XML document needs to have a structure something like this:
<menu>
<chapter>
<heading>heading 1</heading1>
<subheading>subheading 1</subheading>
<subsubheading>subsubheading 1</subsubheading>
<subsubheading>subsubheading 2</subsubheading>
<subheading>subheading 2</subheading>
<subsubheading>subsubheading 1</subsubheading>
</chapter>
<chapter>
<heading>heading 2</heading1>
<subheading>subheading 1</subheading>
<subheading>subheading 2</subheading>
</chapter>
</menu>
So the question - how do I actually get the data from the DB into a fomat something like the above? So far I can read each line and create XML from that but that's no good because it fails to take into account the fact that a heading may have multiple sub headings, and, sub headings may have multiple sub-sub-headings.
The code I am using to do this is as follows but as I say it doesn't do the job correcctly:
<?php
#connect to the database
include "database_connect.php";
$connect = database_connect();
$table_id = 'navigation';
$query = "SELECT * FROM $table_id";
$dbresult = mysql_query($query);
$doc = new DomDocument('1.0');
$root = $doc->createElement('page_construction');
$root = $doc->appendChild($root);
while($row = mysql_fetch_assoc($dbresult)) {
$record = $doc->createElement("chapter");
$record = $root->appendChild($record);
foreach ($row as $fieldname => $fieldvalue) {
$child = $doc->createElement($fieldname);
$child = $record->appendChild($child);
$value = $doc->createTextNode($fieldvalue);
$value = $child->appendChild($value);
}
}
$xml_string = $doc->saveXML();
echo $xml_string;
?>
Many thanks!
