Hi everyone,
The SQL query in the following code block includes a GROUP_CONCAT function:
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 these two.
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's displaying each of the subcategories twice, eg. if 'Healthcare' and 'Hospitality' were both assigned to a particular item, then the following would be output:
Healthcare
Healthcare
Hospitality
Hospitality
The code below is the template file that outputs it to the browser:
If anyone has any ideas about what's happening, would really appreciate any advice.
The SQL query in the following code block includes a GROUP_CONCAT function:
Code:
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 ';
$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
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 these two.
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's displaying each of the subcategories twice, eg. if 'Healthcare' and 'Hospitality' were both assigned to a particular item, then the following would be output:
Healthcare
Healthcare
Hospitality
Hospitality
The code below is the template file that outputs it to the browser:
Code:
<?php include_once(__ROOT__ . "/includes/helpers.inc.php");?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] 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>
<p><a href="..">Return to JMS home</a></p>
</body>
</html>If anyone has any ideas about what's happening, would really appreciate any advice.