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 there are quite a few foreign keys and therefore foreign tables that I need to select at once.

 

Can anyone help me with this query? Would it involve a join?

 

Appreciate any advice.