Page 1 of 1

Multidimensional Array to XML Woes :(

Posted: Tue Jul 15, 2003 12:14 am
by grahama
Can someone take pity on a Newbie?

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 :( It is also the last category in the returned sql query
</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

Posted: Tue Jul 15, 2003 6:42 am
by twigletmac
That's a lot of code - could we please have some [syntax=php][/syntax] and [syntax=php][/syntax] tags to surround it please. (Check the thread linked to in my signature)

Mac

Multidimensional Array to XML Woes :(

Posted: Tue Jul 15, 2003 11:42 am
by grahama
sorry for the long post....
I'll read the posting rules

g