Jump to content

Outputting foreign keys

gwh

By gwh
in PHP Coding Help

Recommended Posts

gwh Member

gwh

Posted
Posted

Hi everyone,

 

The following code is part of code in a controller file that displays a search form:

 

// Display search form
include(__ROOT__ . "/includes/dbAdmin.inc.php");
$result = mysqli_query($link, 'SELECT catID, category FROM categories');
if (!$result)
{
$error = 'Error fetching categories from database!';
include 'error.html.php';
exit();
}

while ($row = mysqli_fetch_array($result))
{
$categories[] = array('catID' => $row['catID'], 'category' => $row['category']);
}

$result = mysqli_query($link, 'SELECT subcatID, subcategory FROM subcategories');
if (!$result)
{
$error = 'Error fetching subcategories from database!';
include 'error.html.php';
exit();
}

while ($row = mysqli_fetch_array($result))
{
$subcategories[] = array('subcatID' => $row['subcatID'], 'subcategory' => $row['subcategory']);
}


$result = mysqli_query($link, 'SELECT itemTypeID, itemType FROM itemTypes');
if (!$result)
{
$error = 'Error fetching item types from database!';
include 'error.html.php';
exit();
}

while ($row = mysqli_fetch_array($result))
{
$itemTypes[] = array('itemTypeID' => $row['itemTypeID'], 'itemType' => $row['itemType']);
}

include 'searchform.html.php';
?>

 

The following is the template file (searchform.html.php) that's included from the above code:

 

<?php include_once(__ROOT__ . "/includes/helpers.inc.php");?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
	<title>Manage items</title>
	<meta http-equiv="content-type"
			content="text/html; charset=utf-8"/>
</head>
<body>
	<h1>Manage items</h1>
	<p><a href="?add">Add new item</a></p>
	<form action="" method="get">
		<p>View items satisfying the following criteria:</p>
		<div>
			<label for="category">By category:</label>
			<select name="category" id="category">
				<option value="">Any category</option>
				<?php foreach ($categories as $category): ?>
					<option value="<?php htmlout($category['catID']); ?>"><?php
							htmlout($category['category']); ?></option>
				<?php endforeach; ?>
			</select>
		</div>
		<div>
			<label for="subcategory">By subcategory:</label>
			<select name="subcategory" id="subcategory">
				<option value="">Any subcategory</option>
				<?php foreach ($subcategories as $subcategory): ?>
					<option value="<?php htmlout($subcategory['subcatID']); ?>"><?php
							htmlout($subcategory['subcategory']); ?></option>
				<?php endforeach; ?>
			</select>
		</div>
		<div>
            
            			<div>
			<label for="itemType">By item type:</label>
			<select name="itemType" id="itemType">
				<option value="">Any item type</option>
				<?php foreach ($itemTypes as $itemType): ?>
					<option value="<?php htmlout($itemType['itemTypeID']); ?>"><?php
							htmlout($itemType['itemType']); ?></option>
				<?php endforeach; ?>
			</select>
		</div>
		<div>

			<label for="text">Containing text:</label>
			<input type="text" name="text" id="text"/>
		</div>
		<div>
			<input type="hidden" name="action" value="search"/>
			<input type="submit" value="Search"/>
		</div>
	</form>
</body>
</html>

 

 

 

When the form in the above template search file is submitted, then the following code in my controller file is executed:

 

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

 

 

The above code builds a query depending on which criteria was entered in the search form. If no search criteria was entered and the submit button is pressed then only the first part of the basic select statement is evaluated:

 

    // The basic SELECT statement

    $select = 'SELECT *';

    $from  = ' FROM items';

    $where  = ' WHERE TRUE';

 

So the above SQL selects all columns in my items table and it's output to the browser via the following template file:

 

<?php include_once(__ROOT__ . "/includes/helpers.inc.php");?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" 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>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['sizeLadiesID']); ?></td>
                    <td><?php htmlout($item['sizeMenID']); ?></td>
				<td><?php htmlout($item['itemPrice']); ?></td>
                    <td><?php htmlout($item['itemColours']); ?></td>
                    <td><?php htmlout($item['catID']); ?></td>
                    <td><?php htmlout($item['supplierID']); ?></td>
                    <td><?php htmlout($item['itemTypeID']); ?></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>
</body>
</html>

 

 

You can see by the array that's constructed in the controller file 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, supplierID, sizeLadiesID and sizeMenID.

 

Here's the array from the abovementioned controller 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 5 foreign tables.

 

Can anyone help me with this query?

 

Appreciate any advice.

 

 

 

 

 

Link to comment
https://forums.phpfreaks.com/topic/190780-outputting-foreign-keys/
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.