...

View Full Version : Explode() on SQL group_concat help please



mar06
06-27-2010, 12:15 PM
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 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>";
$productname = explode(',', $row['products']);
$productimage = explode(',', $row['productimages']);
$paths .= "<id>" . $productname[1] . "</id>";
$paths .= "<image>" . $productimage[1] . "</image>";
$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 :D Thanks very much for your time.

Martin.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum