XML data from mysql with groups and sub groups

Dear Experts,

I am trying to pull data from mysql to xml and Ihave done this, well unprofessionally. I have two tables in my DB one for folder and and the other for file, I have a bit of problem achieving my goal, below is my scrap;

<?php
$db_name = "xxxxx";
$connection = mysql_connect("localhost", "xxxx", "xxxx") or die("Could not connect.");
$table_name = 'pages';
$db = mysql_select_db($db_name);
$query = "select * from " . $table_name .  "GROUP BY category_id ASC";
$result = mysql_query($query, $connection) or die("Could not complete database query");
$num = mysql_num_rows($result);
if ($num != 0) {
 $file= fopen("results.xml", "w");
 $_xml ="<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\r\n";
 $_xml .="<playlist>\r\n";
 while ($row = mysql_fetch_array($result)) {
 if ($row["foldername"]) {
 $_xml .="\t<folder label=\"" . $row["foldername"] . "\">\r\n";
  $_xml .="\t\t<file label=\"" . $row["filename"] . "\" desc=\"" . $row["filedesc"] . "\" />\r\n";
 $_xml .="\t</folder>\r\n";
 } else {
 $_xml .="\t<folder label=\"Nothing Returned\">\r\n";
$_xml .="\t\t<file>none</file>\r\n";
 $_xml .="\t</folder>\r\n";
 }
 }
 $_xml .="</playlist>";
 fwrite($file, $_xml);
 fclose($file);
 echo "XML has been written.  <a href=\"results.xml\">View the XML.</a>";
 } else {
 echo "No Records found";
 } ?>

I intend to have a structure like this in my final xml;

<playlist>
   <folder label="Folder 1">
        <file label="file 1" url="file1.ext" desc="brief desc of the file" />
        <file label="file 2" url="file2.ext" desc="brief desc of the file" />
   </folder>
   <folder label="folder 2">
        <file label="file 3" url="file3.ext" desc="brief desc of the file" />
        <file label="file 4" url="file4.ext" desc="brief desc of the file" />
   </folder>
</playlist>

I will appreciate your usual quick response.

ThanksStart Free Trial