SQL query outputting foreign key instead of correct value

gwh

Hi everyone,

The following code contains a query that selects all columns from my 'items' table. This only happens if there's no search criteria when the form is submitted:

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

// The basic SELECT statement
$select = 'SELECT *';
$from   = ' FROM items';
$where  = ' WHERE TRUE';

$catID =  mysqli_real_escape_string($link, $_GET['category']);
if ($catID != '') // A category is selected
{
	$where .= " AND catID='$catID'";
}

$subcatID =  mysqli_real_escape_string($link,
		$_GET['subcategory']);
if ($subcatID != '') // A subcategory is selected
{
	$from  .= ' INNER JOIN item_to_subcat ON items.itemID = item_to_subcat.itemID';
	$where .= " AND subcatID='$subcatID'";
}

$itemTypeID =  mysqli_real_escape_string($link, $_GET['itemType']);
if ($itemTypeID != '') // An item type is selected
{
	$where .= " AND itemTypeID='$itemTypeID'";
}

$text = mysqli_real_escape_string($link, $_GET['text']);
if ($text != '') // Some search text was specified
{
	$where .= " AND itemTitle LIKE '%$text%'";
}

$result = mysqli_query($link, $select . $from . $where);
if (!$result)
{
	$error = 'Error fetching 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']);
}

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

You can see by the array that's constructed that some of the columns in the table are references to the primary key in a matching table, eg. catID in the items table is the primary key from the categories table. Same goes for itemTypeID and a few others.

Here's the array from the above code:

	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']);
	}

Currently when all the columns are output to the browser, 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 quite a few foreign tables.

Can anyone help me with this query?

Appreciate any advice.

NogDog

I would suggest specifying the exact columns you need returned in the SELECT. If there is any possible ambiguity as to which table each is from, use the table name (or alias) as a prefix, plus you might want to provide an alias:

SELECT table1.foo AS foo, table2.bar AS bar
FROM table1
INNER JOIN table2 ON table2.t1_id = table1.id
. . .

Then you can be assured that the two columns wanted in this example will be indexed as 'foo' and 'bar' in the fetched array for reach result row, plus each result will contain only the necessary data and not any columns that will not be used by the application code.

gwh

NogDog;10940971 wrote:
I would suggest specifying the exact columns you need returned in the SELECT. If there is any possible ambiguity as to which table each is from, use the table name (or alias) as a prefix, plus you might want to provide an alias:
SELECT table1.foo AS foo, table2.bar AS bar
FROM table1
INNER JOIN table2 ON table2.t1_id = table1.id
. . .
Then you can be assured that the two columns wanted in this example will be indexed as 'foo' and 'bar' in the fetched array for reach result row, plus each result will contain only the necessary data and not any columns that will not be used by the application code.

Thanks for the reply,

Just need a bit of clarification.

The table in the database 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';

So since there are 5 foreign tables that need to be dealt with I'm not sure how the query would go or whether it would be best to use a left join instead of an inner join as you suggested. I came up with the following which doesn't include aliases at this stage as I just want to get a working code block before I tweak it with aliases which are advanced for me:

$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, suppliers.supplierID=items.supplierID, itemTypes.itemTypeID=items.itemTypeID, sizesLadies.sizeLadiesID=items.sizeLadiesID, sizesMen.sizeMenID=items.sizeMenID';

It 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.

johanafm

Frankly, I don't know if it is possible to join on the cartesian product of tables in any db, but since the parser protests when you're trying to do this, you're either using bad syntax for it, or it isn't possible. Check your db reference to find out if it should work and what the syntax should be.

Otherwise, do your cartesian product of tables in the FROM clause and then join with the remaining table(s). Once again, check your db reference for join syntax. But it is (usually) on the form

[LEFT|INNER] JOIN table2 ON table2.somefield = table1.somefield
[LEFT|INNER] JOIN ...
[LEFT|INNER] JOIN ...

And you can achieve inner joins through the cartesian product approach if your where clause limits the result set.