Multidimensional Array to XML Woes :(

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
grahama
Forum Newbie
Posts: 6
Joined: Mon Jul 07, 2003 11:57 pm

Multidimensional Array to XML Woes :(

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
Last edited by twigletmac on Wed Jul 16, 2003 3:38 am, edited 1 time in total.
grahama
Forum Newbie
Posts: 6
Joined: Mon Jul 07, 2003 11:57 pm

Multidimensional Array to XML Woes :(

Post by grahama »

sorry for the long post....
I'll read the posting rules

g
Post Reply