MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Common Lookup Tables 1

with one comment

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. You can read further on my second post about common lookup tables.

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 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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. As mentioned above, there’s a newer post on how to leverage common_lookup tables with more working code.

Written by maclochlainn

October 22nd, 2012 at 1:35 am

Posted in HTML,MySQL,PHP

Tagged with ,

«
»