GROUP_CONCAT not working correctly when used with php

gwh

Hi everyone,

The SQL query in the following code block includes a GROUP_CONCAT function:

if (isset($_GET['action']) and $_GET['action'] == 'search')
{
	include(__ROOT__ . "/includes/dbAdmin.inc.php");

$select = '	SELECT 
			items.itemID, 
			itemTitle, 
			itemSKULadies, 
			itemSKUMen, 
			itemDescLadies, 
			itemDescMen,  
			itemPrice, 
			itemColours, 
			categories.category, 
			suppliers.supplier, 
			itemTypes.itemType, 
			sizesMen.size AS Msize, 
			sizesLadies.size AS Lsize, 
			subcategories.subcategory, 
			GROUP_CONCAT(subcategories.subcategory) AS subcategories, 
			siteSections.siteSection';
$from   = '	FROM items 
			LEFT JOIN categories ON categories.catID=items.catID 
			LEFT JOIN suppliers ON suppliers.supplierID=items.supplierID 
			LEFT JOIN itemTypes ON itemTypes.itemTypeID=items.itemTypeID 
			LEFT JOIN sizesMen ON sizesMen.sizeMenID=items.sizeMenID 
			LEFT JOIN sizesLadies ON sizesLadies.sizeLadiesID=items.sizeLadiesID 
			LEFT JOIN item_to_subcat ON item_to_subcat.itemID=items.itemID 
			LEFT JOIN subcategories ON subcategories.subcatID=item_to_subcat.subcatID 
			LEFT JOIN item_to_siteSection ON item_to_siteSection.itemID=items.itemID 
			LEFT JOIN siteSections ON siteSections.siteSectionID=item_to_siteSection.siteSectionID 
			GROUP BY items.itemID';


$result = mysqli_query($link, $select . $from);
if (!$result)
{
	$error = 'Error fetching items: ' . mysqli_error($link);
	include 'error.html.php';
	exit();
}


while ($row = mysqli_fetch_array($result))
{
$items[] = array('itemID' => $row['itemID'], 'itemTitle' => $row['itemTitle'], 'itemSKULadies' => $row['itemSKULadies'], 'itemSKUMen' => $row['itemSKUMen'], 'itemDescLadies' => $row['itemDescLadies'], 'itemDescMen' => $row['itemDescMen'], 'Lsize' => $row['Lsize'], 'Msize' => $row['Msize'], 'itemPrice' => $row['itemPrice'], 'itemColours' => $row['itemColours'], 'category' => $row['category'], 'supplier' => $row['supplier'], 'itemType' => $row['itemType'], 'subcategory' => $row['subcategory'], 'siteSection' => $row['siteSection']);
}

include 'items.html.php';
exit();
}

I have a lookup table called item_to_subcat which stores the relationship between the subcategories and items since there's a many-to-many relationship between the subcategories and the items.

This following line was needed in the above sql query to ensure that even if an item is associated with more than one category, only one unique item is output but each of the subcategories are listed with the item:

GROUP_CONCAT(subcategories.subcategory) AS subcategories,

Currently when it outputs to the browser, it correctly outputs the record but is only listing one subcategory along with it, even though a particular item has at least two subcategories associated with it.

The following is the template file that outputs it to the browser:

<?php include_once(__ROOT__ . "/includes/helpers.inc.php");?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
		"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
	<head>
		<title>Manage items: Search results</title>
		<meta http-equiv="content-type"
				content="text/html; charset=utf-8"/>
	</head>
	<body>
		<h1>Search Results</h1>
		<?php if (isset($items)): ?>
			<table>
				<tr>
                <th>Title</th>
                <th>Ladies SKU</th>
                <th>Men's SKU</th>
                <th>Ladies Description</th>
                <th>Men's Description</th>
                <th>Ladies Sizes</th>
                <th>Men's Sizes</th>
                <th>Price</th>
                <th>Colours</th>
                <th>Category</th>
                <th>Supplier</th>
                <th>Item Type</th>
                <th>Subcategory</th>
                <th>Site Section</th>
                <th>Options</th>
                </tr>
				<?php foreach ($items as $item): ?>
				<tr valign="top">
					<td><?php htmlout($item['itemTitle']); ?></td>
                    <td><?php htmlout($item['itemSKULadies']); ?></td>
                    <td><?php htmlout($item['itemSKUMen']); ?></td>
                    <td><?php htmlout($item['itemDescLadies']); ?></td>
                    <td><?php htmlout($item['itemDescMen']); ?></td>
                    <td><?php htmlout($item['Lsize']); ?></td>
                    <td><?php htmlout($item['Msize']); ?></td>
                   <td><?php htmlout($item['itemPrice']); ?></td>
                    <td><?php htmlout($item['itemColours']); ?></td>
                    <td><?php htmlout($item['category']); ?></td>
                    <td><?php htmlout($item['supplier']); ?></td>
                    <td><?php htmlout($item['itemType']); ?></td>
                    <td><?php htmlout($item['subcategory']); ?></td>
                    <td><?php htmlout($item['siteSection']); ?></td>
					<td>
						<form action="?" method="post">
							<div>
								<input type="hidden" name="itemID" value="<?php
										htmlout($item['itemID']); ?>"/>
								<input type="submit" name="action" value="Edit"/>
								<input type="submit" name="action" value="Delete"/>
							</div>
						</form>
					</td>
				</tr>
				<?php endforeach; ?>
			</table>
		<?php endif; ?>
		<p><a href="?">New search</a></p>
	</body>
</html>

The wierd thing is that when I run the query separately from the php code, it returns the proper results, i.e one unique item record along with all subcategories associated with it. I can't work out why it's only returning one subcat when outputting to the browser via the php code.

If anyone has any ideas about what's happening, would really appreciate any advice.

gwh

False alarm here. I just needed to change the value of a variable. Everything's working now.

johanafm

Just be aware that even though MySQL allows for non standard behaviour for aggregate functions and grouping doesn't mean that you really get the results you expect (even though you might).

The standard says that every single column needs to either be an aggregate function or part of the group by clause. MySQL doesn't enforce this, but to handle non-confirming queries it selects a value from an arbitrary row for every column not part of an aggregate function or the group by clause.

For example

tbl
id  name  val
1   adam  10
2   adam   5
3   david  4

SELECT id, name, SUM(val) AS s
FROM tbl
GROUP BY name

-- will show
id  name  s
?   adam  15
3   david 4

The ? next to adam can be either 1 or 2 and may change if you apply and ORDER BY or possibly if you start applying joins etc. It is possible that this value is always the first or last value encountered (I wouldn't know cause I'd stay far away from queries like this), in which case it would be possible to predict the outcome. But do not forget that without an order by clause the data set should not be considered ordered in any particular way.
As such, any non-aggregate function/group by column value has no direct relation to the rest of the data show. The only thing you know about the id shown above is that "one of the underlying rows for the data presented contains this id".

So actually selecting fields with neither an aggregate function or in the group by clause gives no reliable information. Unless the value is unique, as it was for david, in which case neither the aggregate function(s) nor the group by clause has any bearing for the rest of that data (david and 4).