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:
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 catLEFT OUTER JOIN subCategory ON subCategory.parentID = cat.categoryIDLEFT OUTER JOIN category AS subcat ON subcat.categoryID = subCategory.categoryIDLEFT 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.categoryIDORDER BY cat.categoryName , subcat.categoryNamewhich 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.