creating an XML datafeed

problem with creating categories belonging to the product

[webdevfv]

Hi

Probably a simple one for you all. I'm trying to create the following XML structure:

<product>
<product_id>00001</product_id>
<brand>Big Brand</brand>
<model>Fast</model>
<categories>
<category>
<category_id>20</category_id>
<category_name>Traditional things</category_name>
</category>
<category>
<category_id>12</category_id>
<category_name>Other things</category_name>
</category>
</categories>
</product>
<product>
<product_id>00002</product_id>
<brand>Big Brand</brand>
<model>Slow</model>
<categories>
<category>
<category_id>20</category_id>
<category_name>Traditional things</category_name>
</category>
<category>
<category_id>6</category_id>
<category_name>Stuff</category_name>
</category>
</categories>
</product>

$result = mysql_query("SELECT pro.id as id, make, model, category, cat.cat_id as cat_id FROM products pro LEFT JOIN categories_lookup catl ON pro.id = catl.id LEFT JOIN categories cat ON cat.cat_id = catl.cat_id ORDER BY id, category DESC");

$last_id = "";
while ($row = mysql_fetch_array($result)) {
$id= $row['id'];
$cat_id= $row['cat_id'];
$category= $row['category'];
$make= $row['make'];
$model= $row['model'];

if ($row['id']!= $last_id)
{

$cats = "
<category>
<category_id>{$row['cat_id']}</category_id>
<category_name>{$row['category']}</category_name>
</category>
";

$display_block .= "
<product>
<product_id>{$row['id']}</product_id>
<brand>{$row['make']}</brand>
<model>{$row['model']}</model>
<categories>$cats</categories>
</product>";
$last_id = $row['id'];
}
}

But I'm getting a little stuck. The code below adds the categories to each entry resulting in the last product having lots of categories that are not connected to it. Where am I going wrong?

TIA
Kevin

[coopster]

Have a closer look at your logic here. Sometimes it is easier to write it out on paper, then program the code.

1. Start a Product Block
   
2. Build Category Block(s)
   
3. If product block changed, close current product block, start over
   
4. After last loop iteration we will not have noticed a product block change so we need to close the current product block

I think you what you want here is to build category blocks until you notice a change in the product id. Once you notice that the product id has changed, you need to build a new product block and once again start building category blocks.