Hi all,
Thanks for taking the time to read my post.

I've got a complex SQL query that outputs categories, their related categories, and product name(s) and image(s) related to the main category.

To resolve cross-join effects, i was advised to use group_concat on product information at the end of the table, so it bases the products on the main categories and not the related categories, which works brilliantly.

My query:

Code:
SELECT cat.categoryName AS categoryName
     , cat.categoryDescription
     , cat.categoryImage
     , subcat.categoryName AS subcategoryName
     , subcat.categoryDescription AS subcategoryDescription
     , subcat.categoryImage AS subcategoryImage
     , p.product_names AS productname
     , p.product_images AS productimages
  FROM category AS cat
LEFT OUTER 
  JOIN subCategory 
    ON subCategory.parentID = cat.categoryID
LEFT OUTER 
  JOIN category AS subcat 
    ON subcat.categoryID = subCategory.categoryID
LEFT OUTER 
  JOIN ( SELECT categoryproduct.categoryID
              , GROUP_CONCAT(product.productName) AS product_names, GROUP_CONCAT(product.productImage) AS product_images
           FROM categoryproduct 
         INNER 
           JOIN product 
             ON categoryproduct.productID = product.productID
         GROUP
             BY categoryproduct.categoryID
       ) AS p
    ON p.categoryID = cat.categoryID
ORDER 
    BY cat.categoryName
     , subcat.categoryName

which outputs:

category_name, related_category, productname, productimages

category1 | category3 | NULL | NULL
category2 | NULL | product1 | prod1imageurl
category3 | category1 | NULL | NULL
category4 | category1 | product2,product3,product4 | prod2imageurl,prod3imageurl,prod4imageurl
category4 | category2 | product2,product3,product4 | prod2imageurl,prod3imageurl,prod4imageurl
category4 | category3 | product2,product3,product4 | prod2imageurl,prod3imageurl,prod4imageurl

My PHP Code:

PHP Code:
<?php
header
("Content-type: text/xml");

$xml_output "<?xml version=\"1.0\"?>\n"
$xml_output .= "<categories>\n"

dbConnect();
$result mysql_query("



SELECT cat.categoryName AS categoryName
     , cat.categoryDescription
     , cat.categoryImage
     , subcat.categoryName AS subcategoryName
     , subcat.categoryDescription AS subcategoryDescription
     , subcat.categoryImage AS subcategoryImage
     , p.product_names AS products
     , p.product_images AS productimages
  FROM category AS cat
LEFT OUTER 
  JOIN subCategory 
    ON subCategory.parentID = cat.categoryID
LEFT OUTER 
  JOIN category AS subcat 
    ON subcat.categoryID = subCategory.categoryID
LEFT OUTER 
  JOIN ( SELECT categoryproduct.categoryID
              , GROUP_CONCAT(product.productName) AS product_names, GROUP_CONCAT(product.productImage) AS product_images
           FROM categoryproduct 
         INNER 
           JOIN product 
             ON categoryproduct.productID = product.productID
         GROUP
             BY categoryproduct.categoryID
       ) AS p
    ON p.categoryID = cat.categoryID
ORDER 
    BY cat.categoryName
     , subcat.categoryName
"
);

$previousCategory "";
$previousSubCategory "";
$previousPath "";
$paths "";
$ymal "";

while(
$row mysql_fetch_assoc($result)) 
{       
    if (
$row['categoryName'] != $previousCategory) {  
        if (
$previousCategory != "") {
            
$paths .= "</paths>";
            
$ymal .= "</ymal>";
            
$xml_output .= $ymal "</category>";         
            
$paths '';
            
$ymal '';      
        }
        
$xml_output .= "<category>";
        
$xml_output .= "<title>" $row['categoryName'] . "</title>";
        
//$xml_output .= "<description><![CDATA[" . $row['categoryDescription'] . "]]></description>";
        
$xml_output .= "<image>" $row['categoryImage'] . "</image>";
        
$paths "<paths>";
        
$ymal "<ymal>";
    }
    if (
$row['products'] != $previousPath) { 

    
$paths .= "<opt>";
    [
B]$productname explode(','$row['products']);
    
$productimage explode(','$row['productimages']);
    
$paths .= "<id>" $productname[1] . "</id>";
    
$paths .= "<image>" $productimage[1] . "</image>";[/B]
        
$paths .= "</opt>";

    }
    if (
$row['subcategoryName'] != $previousSubCategory) {
    
$ymal .= "<opt>";
    
$ymal .= "<id>" $row['subcategoryName'] . "</id>";
    
$ymal .= "<title>" $row['subcategoryName'] . "</title>";
    
$ymal .= "<image>" $row['subcategoryImage'] . "</image>";
    
$ymal .= "</opt>";
    }
$previousCategory $row['categoryName']; 
$previousSubCategory $row['subcategoryName']; 
$previousPath $row['products']; 
}
if (
mysql_num_rows($result) > 0)
{
    
$xml_output .= $paths;
    
$xml_output .= "</paths>";
    
$xml_output .= $ymal;
    
$xml_output .= "</ymal>";
    
$xml_output .= "</category>";         
}  

dbClose();
$xml_output .= "</categories>"

echo 
$xml_output;
?>
I'm outputting to XML, so would need the array of both product name and image to be displayed as:

- <paths>
- <opt>
<id>product</id>
<image>product</image>
</opt>
- <opt>
<id>product</id>
<image>product</image>
</opt>
- <opt>
<id>product</id>
<image>product</image>
</opt>
</paths>

I've tried to output both, but it ends up not displaying any XML. Could anyone help me to output both group_concat arrays under each XML please?

This is the last thing to do, and then this project is finished Thanks very much for your time.

Martin.