Everything seems to work except the XML output
The SqL query works, the array is populated with the correct data
Problem
The arrays generated from the two queries do not seem to jive with one another in the xml output?
Can some brighter soul take a quick look and tell me what I am doing wrong?
---------------------------------------------------
The script:
<?php
// Declare all variables
//$conn = 0;
$sql = '';
$result = '';
$arr = array();
$category = array();
$xml = '';
@ $db = mysql_connect('127.0.0.1','root','musiccows');
if (!$db)
{
echo 'Error:Could Not Connect';
exit;
}
mysql_select_db('sirenreels');
/// Query the database to get categories included in the reels table
$sql = 'SELECT category.id, category.name ';
$sql .= 'FROM category, reel ';
$sql .= 'WHERE reel.category_id = category.id ';
$sql .= 'GROUP BY category.id LIMIT 0, 30';
$result = mysql_query($sql);
/*
Ok, now you have the list returned from the above query
id name
_________________________________
1 West Coast Show Reel
2 East Coast Show Reel
3 Toy Reel
4 Sound Design
*/
while ($arr = mysql_fetch_array($result)) {
$category[$arr['category_id']]['name'] = $arr['name'];
//test out array
echo $arr['name']."\n";
}
// Now query the database for the songs ///
$sql = 'SELECT reel.category_id, category.name, reel.spot_id, spot.title, composer.id, composer.fname, ';
$sql .= 'composer.lname ';
$sql .= 'FROM reel, spot, category, composer ';
$sql .= 'WHERE reel.category_id = category.id AND reel.spot_id = spot.id AND spot.composer_id = composer.id ';
$sql .= 'ORDER BY category.id ASC LIMIT 0, 60 ';
$result = mysql_query($sql);
///now that you have the categories, loop thru the songs
for($i =0; $arr = mysql_fetch_array($result); $i++) {
$category [$arr['category_id']] ['spot'][$i]['id'] = $arr['spot_id'];
$category [$arr['category_id']] ['spot'][$i]['title'] = $arr['title']; $category [$arr['category_id']] ['spot'][$i]['fname'] = $arr['fname'];
$category [$arr['category_id']] ['spot'][$i]['lname'] = $arr['lname'];
//test out the array
echo "\n".$arr['spot_id']."|".$arr['title']."|".$arr['fname']."|".$arr['lname']."\n";
}
/// Create XML to return to the movie
print "<sirenreels>\n";
foreach ($category as $key => $value)
{
$xml .= "<category>\n";
$xml .= "<meta>\n";
$xml .= "<title>".$category[$key]['name']."</title>\n";
$xml .= "</meta>\n";
$xml .= "<content>\n";
foreach ( $category[$key] ['spot'] as $value)
{
$xml .= "<spot>\n";
$xml .= "<id>".$value['id']."</id>\n";
$xml .= "<title>".$value['title']."</title>\n";
$xml .= "<composer>".$value['fname'];
$xml .= " ".$value['lname']."</composer>\n";
$xml .= "</spot>\n";
}
$xml .= "</content>\n";
$xml .= "</category>\n";
}
$xml .= "</sirenreels>\n";
print $xml;
?>
It seems that the xml string does not know what the category name is
it gives an empty string <title></title>
----------------------------------------------------------------
Some of the Source XML output:
<sirenreels>
<category>
<meta>
<title>Toy Reel</title> //this is the only category shown...not sure why it is here with no associated spots
</meta>
</category>
<category>
<meta>
<title></title> ///no category....this is the case for all new categories in the XML string
</meta>
<spot> ///////////////all the spots seem to be fine
<id>10</id>
<title>taste</title>
<composer>Atticus Ross</composer>
</spot>
<spot>
<id>8</id>
<title>mama said</title>
<composer>Eric Lee</composer>
</spot>
<spot>
<id>13</id>
<title>color adjustment</title>
<composer>Graham Anderson</composer>
</spot>
<spot>
<id>26</id>
<title>alarm</title>
<composer>Atticus Ross</composer>
</spot>
<spot>
<id>12</id>
<title>ellen feiss</title>
<composer>John Murphy</composer>
</spot>
<spot>
<id>16</id>
<title>skate</title>
<composer>Tim Boland</composer>
</spot>
<spot>
<id>14</id>
<title>joan</title>
<composer>Andrew Dog</composer>
</spot>
<spot>
<id>17</id>
<title>dreams</title>
<composer>Graham Anderson</composer>
</spot>
<spot>
<id>27</id>
<title>drive</title>
<composer>Tim Boland</composer>
</spot>
<spot>
<id>9</id>
<title>delay</title>
<composer>Tim Boland</composer>
</spot>
<spot>
<id>15</id>
<title>hey</title>
<composer>Chris Desmond</composer>
</spot>
</content>
</category>
<category>
<meta>
<title></title> ///no title given for the category
</meta>
<content>
<spot>
<id>8</id>
<title>mama said</title>
<composer>Eric Lee</composer>
</spot>
<spot>
<id>9</id>
<title>delay</title>
<composer>Tim Boland</composer>
</spot>
<spot>
<id>27</id>
<title>drive</title>
<composer>Tim Boland</composer>
</spot>
<spot>
<id>10</id>
<title>taste</title>
<composer>Atticus Ross</composer>
</spot>
<spot>
<id>11</id>
<title>smooth ride</title>
<composer>Kendall Marsh</composer>
</spot>
<spot>
<id>12</id>
<title>ellen feiss</title>
<composer>John Murphy</composer>
</spot>
<spot>
<id>13</id>
<title>color adjustment</title>
<composer>Graham Anderson</composer>
</spot>
<spot>
<id>14</id>
<title>joan</title>
<composer>Andrew Dog</composer>
</spot>
<spot>
<id>15</id>
<title>hey</title>
<composer>Chris Desmond</composer>
</spot>
</content>
</category>
......
---------------------------------------------------------------------------
many humble thanks
graham