Outputting the value of a foreign key

Hi everyone,

I have a table in my database that consists of 13 columns and five of those columns are foreign keys referencing the primary key in their related table. These are the columns with the last 5 being the foreign keys:

itemID,
itemTitle,
itemSKULadies,
itemSKUMen,
itemDescLadies,
itemDescMen,
itemPrice,
itemColours,
sizeLadiesID,
sizeMenID,
catID,
supplierID,
itemTypeID';

I can query the database as follows and store the results in an array:

	// Build the list of columns from the items table
	$sql = "SELECT * FROM items";
	$result = mysqli_query($link, $sql);
	if (!$result)
	{
		$error = 'Error fetching list of items.';
		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'], 'sizeLadiesID' => $row['sizeLadiesID'], 'sizeMenID' => $row['sizeMenID'], 'itemPrice' => $row['itemPrice'], 'itemColours' => $row['itemColours'], 'catID' => $row['catID'], 'supplierID' => $row['supplierID'], 'itemTypeID' => $row['itemTypeID']);

PHP:

I can then output the result to the browser but if I do this then the columns that are foreign keys are showing the primary key from the related table. I need the actual value from the foreign table to be output instead of just the key. I know this would involve revising the SQL query but I'm not sure how to do it especially since it involves 5 foreign tables.

Can anyone help with this?

Appreciate any assistance.

 

select items.*, supplier.name as supplier_name
from items, supplier
where items.supplierID = supplier.supplierID

 

Thanks for the reply,

I noticed your SQL only mentions one foreign table. I explained in my original post that there are 5 foreign tables to deal with.

I came up with the following code which uses a left join:

$sql = 'SELECT itemID, itemTitle, itemSKULadies, itemSKUMen, itemDescLadies, itemDescMen, itemPrice, itemColours, categories.category, suppliers.supplier, itemTypes.itemType, sizesMen.size, sizesLadies.size
FROM items LEFT JOIN categories, suppliers, itemTypes, sizesMen, sizesLadies
WHERE categories.catID=items.catID, suppliers.supplierID=items.supplierID, itemTypes.itemTypeID=items.itemTypeID, sizesLadies.sizeLadiesID=items.sizeLadiesID, sizesMen.sizeMenID=items.sizeMenID';

The code gave me the following error though:

Error fetching items: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' suppliers, itemTypes, sizesMen, sizesLadies WHERE categories.catID=items.catID,' at line 1

Can you comment further on this SQL code as I'm not sure what the error means.

Appreciate any further advice.

 

Between the "where" parts, use the word "and" instead of a comma.

 

I tried that alteration as follows:

	$select = 'SELECT itemID, itemTitle, itemSKULadies, itemSKUMen, itemDescLadies, itemDescMen,  itemPrice, itemColours, categories.category, 						suppliers.supplier, itemTypes.itemType, sizesMen.size, sizesLadies.size'; 
$from   = ' FROM items LEFT JOIN categories, suppliers, itemTypes, sizesMen, sizesLadies';
$where  = ' WHERE categories.catID=items.catID and suppliers.supplierID=items.supplierID and itemTypes.itemTypeID=items.itemTypeID and sizesLadies.sizeLadiesID=items.sizeLadiesID and sizesMen.sizeMenID=items.sizeMenID';

PHP:

I'm still getting the same error.

Any further suggestions really appreciated.

 

Sorry, I didn't notice the join there - I just pointed out the first error that caught my eye.

If you are doing qualified joins, you have to specify the matching columns in the join clause.

from items left join categories on catID left join suppliers on supplierID .... etc etc

and then you don't need the wheres that are defining those same relationships.

I suggest you take some time to read the MySQL online documentation which is very detailed and has lots of good examples. It will make your life much easier.

 

I revised the syntax as you suggested and this has worked:

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

PHP:

But you'll notice that up in the select part of the query two of the tables have the same column name, ie.

sizesMen.size, sizesLadies.size

So in both the sizesMen and sizesLadies table, both columns are called 'size' which is why I had to specify explicitly the table names here.

The problem is that when the results are output in an array as follows, I don't know how to differentiate between the two identically named columns:

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'], 'size' => $row['size'], 'sizeMenID' => $row['sizeMenID'], 'itemPrice' => $row['itemPrice'], 'itemColours' => $row['itemColours'], 'category' => $row['category'], 'supplier' => $row['supplier'], 'itemType' => $row['itemType']);
	}

PHP:

In the above code I specified the following to represent the ladies size and it output correctly:

'size' => $row['size'],

But I don't know how to do this for the men's size as it's identical to the ladies:

'sizeMenID' => $row['sizeMenID'],

I need to change the above to something like the ladies but don't know how to differentiate it. I don't really want to go back and change the column names in the database as this will wreck up a lot of other code.

Do you have any suggestions here?

Appreciate your support.

 

I ended up using aliases to solve this last problem so everything's working now.

Thanks again for your help.