SQL syntax

AEM151 · February 4, 2010, 2:53am

Hi everyone,

The following SQL code is using outer joins to get the information from a main items table as well as 5 foreign tables, but in addition to this I need to output a column from one other foreign table, ie. the subcategories table.

$select = 'SELECT itemID, itemTitle, itemSKULadies, itemSKUMen, itemDescLadies, itemDescMen,  itemPrice, itemColours, categories.category,                                              suppliers.supplier, itemTypes.itemType, sizesMen.size AS Msize, sizesLadies.size AS Lsize, subcategories.subcategory';
$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 subcategories.subcatID=item_to_subcat.subcatID';
$where  = ' WHERE TRUE';

This table has a many to many relationship with the main items table so instead of the foreign key being stored in the items table (as they are for the other 5 foreign keys), this table has its own look up table called item_to_subcat.

So I need to extend the SQL to output the subcategory from the subcategories table. I tried to do this in the code below by firstly adding the following to the end of the select part of the code:

subcategories.subcategory

Then I added another left join as follows onto the from part of the code:

LEFT JOIN item_to_subcat ON subcategories.subcatID=item_to_subcat.subcatID

When I tested I got the following error:

Error fetching items: Column ‘itemID’ in field list is ambiguous

Does anyone what this could mean or how to fix this?

r937 · February 4, 2010, 3:49am

try like this –

SELECT itemID
     , itemTitle
     , itemSKULadies
     , itemSKUMen
     , itemDescLadies
     , itemDescMen
     , itemPrice
     , itemColours
     , categories.category
     , suppliers.supplier
     , itemTypes.itemType
     , sizesMen.size AS Msize
     , sizesLadies.size AS Lsize
     , subcategories.subcategory
  FROM items 
LEFT OUTER
  JOIN categories 
    ON categories.catID = items.catID 
LEFT OUTER
  JOIN suppliers 
    ON suppliers.supplierID = items.supplierID 
LEFT OUTER
  JOIN itemTypes 
    ON itemTypes.itemTypeID = items.itemTypeID 
LEFT OUTER
  JOIN sizesMen 
    ON sizesMen.sizeMenID = items.sizeMenID 
LEFT OUTER
  JOIN sizesLadies 
    ON sizesLadies.sizeLadiesID = items.sizeLadiesID 
[COLOR="Blue"]LEFT OUTER
  JOIN item_to_subcat 
    ON item_to_subcat.itemID = items.itemID
LEFT OUTER
  JOIN subcategories
    ON subcategories.subcatID = item_to_subcat.subcatID [/COLOR]

AEM151 · February 4, 2010, 7:40am

Thanks - that’s great. All working except for one thing:

Since an item can belong to more than one subcategory, it’s outputting two identical records - well nearly identical since each of them have a different subcategory. Is there a way to limit the results so that only one record is output but it shows all subcategories that the record belongs to?

r937 · February 4, 2010, 9:45am

yes

SELECT itemID
     , itemTitle
     , itemSKULadies
     , itemSKUMen
     , itemDescLadies
     , itemDescMen
     , itemPrice
     , itemColours
     , categories.category
     , suppliers.supplier
     , itemTypes.itemType
     , sizesMen.size AS Msize
     , sizesLadies.size AS Lsize
     , [COLOR="blue"]GROUP_CONCAT(subcategories.subcategory) AS subcategories[/COLOR]
  FROM items 
LEFT OUTER
  JOIN categories 
    ON categories.catID = items.catID 
LEFT OUTER
  JOIN suppliers 
    ON suppliers.supplierID = items.supplierID 
LEFT OUTER
  JOIN itemTypes 
    ON itemTypes.itemTypeID = items.itemTypeID 
LEFT OUTER
  JOIN sizesMen 
    ON sizesMen.sizeMenID = items.sizeMenID 
LEFT OUTER
  JOIN sizesLadies 
    ON sizesLadies.sizeLadiesID = items.sizeLadiesID 
LEFT OUTER
  JOIN item_to_subcat 
    ON item_to_subcat.itemID = items.itemID
LEFT OUTER
  JOIN subcategories
    ON subcategories.subcatID = item_to_subcat.subcatID 
[COLOR="Blue"]GROUP
    BY items.itemID [/COLOR]

AEM151 · February 5, 2010, 2:41am

Thanks again,

This has limited the results to just the one record but it only outputs the first subcategory in the list. Since some records have more than one subcategory, is it possible to output both? If yes, would this involve revising the query or would it have something to do with the php?

r937 · February 5, 2010, 2:55am

in the result set produced by the query, the column called categories will have a comma-delimited list of the categories for each item

run the query outside of php to see what i mean

AEM151 · February 5, 2010, 3:57am

Yeah I see what you mean after running the query separately but for some reason when used with the php it’s only ouputting the first subcategory. This is the code that outputs it to the browser:

		<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; ?>

I don’t know what’s going on.

AEM151 · February 5, 2010, 4:03am

This is the code that captures the result set:


	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();
}

r937 · February 5, 2010, 8:26am

it’s really simple – your php is not allowed to handle a query result set column that doesn’t exist

if you look at the query that i gave you, which i simply adapted from the stuff you posted in post #1 of this thread, there is no column called “category” in the query i gave you, but there is a colulmn called “categories”

you must go through your actual query, and, column by column, make sure that you are using the correct column names in your php code as well

such are the rigours of database development – a keen and methodical eye, and attention to detail

AEM151 · February 5, 2010, 10:31am

I’m not sure why you’re mentioning category as it’s the subcategory that’s the subject of the many to many table and the code you’ve given me.

you must go through your actual query, and, column by column, make sure that you are using the correct column names in your php code as well

I’ve double checked all the column names and they’re correct. They must be right since it’s outputting the subcategory but as mentioned it’s just not including the second subcategory when outputting (even though it seems to be including it in a result set when I run the query separately).

The following is the current code:


```php
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' =&gt; $row['itemID'], 'itemTitle' =&gt; $row['itemTitle'], 'itemSKULadies' =&gt; $row['itemSKULadies'], 'itemSKUMen' =&gt; $row['itemSKUMen'], 'itemDescLadies' =&gt; $row['itemDescLadies'], 'itemDescMen' =&gt; $row['itemDescMen'], 'Lsize' =&gt; $row['Lsize'], 'Msize' =&gt; $row['Msize'], 'itemPrice' =&gt; $row['itemPrice'], 'itemColours' =&gt; $row['itemColours'], 'category' =&gt; $row['category'], 'supplier' =&gt; $row['supplier'], 'itemType' =&gt; $row['itemType'], 'subcategory' =&gt; $row['subcategory'], 'siteSection' =&gt; $row['siteSection']);
	}
	
	include 'items.html.php';
	exit();
}



It seems the code is working but only outputting the one subcategory. I'm totally confused.

AEM151 · February 5, 2010, 11:23am

I worked out what was wrong.

I just changed this line:

GROUP_CONCAT(subcategories.subcategory) AS subcategories

to

GROUP_CONCAT(subcategories.subcategory) AS subcategory

Now it’s outputting them, but still another problem though. If an item has only one subcategory assigned to it, say ‘healthcare’, for some reason it’s outputting that one subcategory twice so it’s outputting:

healthcare, healthcare

Any idea why it’s doing this?

Appreciate any further help.

AEM151 · February 5, 2010, 11:51am

The abovementioned problem may have its source in other parts of the php code but as I wasn’t sure whether the SQL was contributing to it, I thought I’d just check first.

r937 · February 5, 2010, 1:24pm

in your query, you have this –

SELECT ...
       [COLOR="Red"]subcategories.subcategory[/COLOR],   
       GROUP_CONCAT(subcategories.subcategory) AS subcategories,

that’s wrong

the query i gave you is a GROUP BY query, which groups all the subcategories together, using the GROUP_CONCAT function

therefore an individual subcategory is not available

remove the line in red

guelphdad · February 5, 2010, 3:44pm

Hey Rudy, you aren’t doing PHP there are ya buddy?

r937 · February 5, 2010, 4:06pm

no, guelphdad, just SQL

AEM151 · February 8, 2010, 1:57am

Thanks again. I think all the SQL is right now but I’m still experiencing the problem where if an item has only one subcategory assigned to it, it’s outputting that one subcategory twice. I think it could have something to do with the php which I’ll have to check.

AEM151 · February 8, 2010, 5:09am

I tried checking the php but couldn’t find any errors. Here’s the current SQL again:

	$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, 
				GROUP_CONCAT(subcategories.subcategory SEPARATOR ", ") AS subcategory, 
				GROUP_CONCAT(siteSections.siteSection SEPARATOR ", ") AS 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';

Is there anything in there that might be causing this?

r937 · February 8, 2010, 5:42am

it’s your data

it could be any one of those joins causing it

any join which has a one-to-many relationship could be doubling the result rows

AEM151 · February 8, 2010, 7:47am

Only the subcategories and siteSections have one-to-many relationships.

Not sure what you mean here. Why could this happen and how do you resolve it? I thought that GROUP_CONCAT was supposed to prevent this?

r937 · February 8, 2010, 1:09pm

then it’s possible that a site has 2 subsections and 3 subcategories? the FROM clause will return 6 rows for that, and your GROUP_CONCATs will contain duplicates