My students wanted an example of how to use a lookup table in the database. I thought it would be a great idea to create a simple example like this one.
A lookup table is a generalization that holds lists of values that support end-user selections. The following example uses a combination of the common_lookup_table and common_lookup_column columns to identify sets of value for drop down lists. The end-user selects a value from the list to identify a unique row, and returns a common_lookup_id surrogate key value.
The sample code uses the table defined in the previous illustration. It uses a simple HTML drop down list, a PHP library.inc file, and an HTML display form. Below is the drop down selection set for a table and column value.
The drop down list code uses an insecure and trivial GET method to keep the example simple, as shown below:
<html>
<header>
<title>Select Option Sample</title>
<style type="text/css">
/* Class tag element styles. */
.label {min-width:200px;text-align:left;}
.title {font-weight:bold;font-style:italic;font-size:125%;}
</style>
</header>
<body>
<?php
// Include libraries.
include_once("library.inc");
// Declare input variables.
$table_name = (isset($_GET['table_name'])) ? $_GET['table_name'] : $table_name = "item";
$column_name = (isset($_GET['column_name'])) ? $_GET['column_name'] : $column_name = "item_type";
// Call function.
get_lookup($table_name, $column_name);
?>
</body>
</html>
The library.inc file contains the logic to use a PHP prepared statement to read and render a SELECT HTML tag. It uses OPTION tags for all values in the drop down list. The values for the OPTION tag are the surrogate key values from the common_lookup_id column of the common_lookup table, and text elements are the descriptive values from the common_lookup_meaning column.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
<?php
/*
|| Program Name: library.inc
*/
function get_lookup($table_name, $column_name) {
// Assign credentials to connection.
$mysqli = new mysqli("localhost", "student", "student", "studentdb");
// Check for connection error and print message.
if ($mysqli->connect_errno) {
print $mysqli->connect_error."<br />";
print "Connection not established ...<br />";
}
else {
// Initial statement.
$stmt = $mysqli->stmt_init();
// Declare a static query.
$sql = "SELECT cl.common_lookup_id\n"
. ", cl.common_lookup_meaning\n"
. "FROM common_lookup cl\n"
. "WHERE common_lookup_table = ?\n"
. "AND common_lookup_column = ?\n"
. "ORDER BY 2";
// Prepare statement.
if ($stmt->prepare($sql)) {
$stmt->bind_param("ss",$table_name,$column_name); }
// Loop through a result set until completed.
do {
// Attempt query and exit with failure before processing.
if (!$stmt->execute()) {
// Print failure to resolve query message.
print $mysqli->error."<br />";
print "Failed to resolve query ...<br />";
}
else {
// Fetch a row for processing.
$result = $stmt->get_result();
// Print the opening select tag.
print '<form method="post" name="myForm" action="submitItemType.php">';
print '<div class="title">Select unique from list:</div>';
print '<select name="item_type" size="1" onChange="change(this.form.item_type)">';
print "<option class=label value='' selected>Select </option>";
// Read through the rows of the array.
while( $row = $result->fetch_array(MYSQLI_NUM) ) {
print "<option class=label value='".$row[0]."'>".$row[1]."</option>";
}
}
} while($stmt->next_result());
// Print the closing HTML table tag.
print '</select>';
print '<input name="submit" type="submit" value="Submit">';
print '</form>';
// Release connection resource.
$mysqli->close(); }
}
?>
The display form action calls the submitItemType.php program, which displays the value from the OPTION tag selected in the prior form. The code for the display is:
<html> <head> </head> <body> <?php print "ITEM_TYPE -> [".$_POST['item_type']."]"; ?> </body> </html>
It generates:
Hope this helps illustrate the value of and mechanics of lookup tables.