Final question xml php almost there check it

programming
1

okay I am trying to create a menu via xml which xml is generated via php and mysql.
I am trying to link all pages under their corresponding subjects here is how I am going about it.


function flash_navigation() {
		global $connection;
		$query = "SELECT a.menu_name, b.subject_name FROM ";
		$query .= " pages a ";
		$query .= " inner join subjects b ";
		$query .= " on b.id = a.subject_id ";
		$result = mysql_query($query, $connection) or die("Could Not Get");
		$num = mysql_num_rows($result);
		if ($num != 0) {
		$file= fopen("results.xml", "w");

 		$_xml ="<?xml version=\"1.0\" encoding=\"UTF-8\" ?>
";

 		$_xml .="<menu>
";

		while ($row = mysql_fetch_array($result)) {

 		if ($row['subject_name']) {

                $_xml .="	<item name=\"" . $row['subject_name'] . "\">
";

                $_xml .="		<sub name=\"" . $row['menu_name'] . "\"/>
";
                $_xml .="	</item>
";
     } else {

 $_xml .="	<item name=\"Nothing Returned\">
";
$_xml .="		<sub name =\"Nothing Returned\">
";

 $_xml .="	</item_name>
";
 } 
}

 $_xml .="</menu>";

 fwrite($file, $_xml);

 fclose($file);

 echo "XML has been written.  <a href=\"results.xml\">View the XML.</a>";

 } else {

 echo "No Records found";

 }
}

This is returning somewhat usable xml but it is not grouping all pages with subjects instead creates a new item name tag if there is another page that corresponds with a subject. Like below. Also if a project is in database but has no pages correlating it doesnt return the project.


<?xml version="1.0" encoding="UTF-8" ?>
<menu>
	<item name="Residential 1">
		<sub name="dude"/>
	</item>
	<item name="Residential 1">
		<sub name="woompa"/>
	</item>
	<item name="Residential 2">
		<sub name="masa"/>
	</item>
	<item name="Commercial 1">
		<sub name="gatorade"/>
	</item>
	<item name="Commercial 2">
		<sub name="okao"/>
	</item>
</menu>

How can I group them where it would be like:


<?xml version="1.0" encoding="UTF-8" ?>
<menu>
	<item name="Residential 1">
		<sub name="dude"/>
                <sub name="woompa"/>
	</item>
	<item name="Residential 2">
		<sub name="masa"/>
	</item>
	<item name="Commercial 1">
		<sub name="gatorade"/>
	</item>
	<item name="Commercial 2">
		<sub name="okao"/>
	</item>
	<item name="Residential 1">  <<<<< Here it would show even if no pages	
	</item>
</menu>

I have tried setting another if statement correlating with proper $row and only if menu_name was present but it does not function. Any ideas on how to create this?

MT