Explode() for XML output using group_concat in SQL query

[mar06]

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 
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.