Generating Form Elements from Lookup Tables and Query Results

only for RuBoard - do not distribute or recompile

Up to this point in the chapter, we ve concentrated on ways to automate form processing by consulting the information that MySQL provides in table descriptions. In this section, we ll discuss how to generate form elements from table contents rather than table structure. This technique is used most commonly to generate list elements corresponding to enumerations and to sets that is, lists for which you must pick a single value and lists from which you are allowed to pick multiple values.

Using Lookup Tables for Enumerations

Here s a simple example where you might want to use a lookup table to generate a form element. Suppose you have several applications that each present a form containing a pop-up menu element for specifying a salutation ( Mr. , Mrs. , and so on). But you don t want to write out all the salutations literally in every script. One way to avoid this is to declare the salutation column as an ENUM in the underlying table associated with each form:

 salutation ENUM('Mr.', 'Mrs.', 'Miss', 'Ms.', 'None') NOT NULL DEFAULT 'None' 

Then you can create the pop-up menu automatically from the column description in the table information. But you don t want to do that, either; because if you decide to add a new salutation (such as Dr. ), you d need to change the ENUM definition in all tables affected by the change.

In this situation, another way to deal with the problem is to list your salutations in a separate lookup table. This allows your scripts to generate the pop-up menu automatically from the contents of this table (we ll see how shortly), but it also enables you to change the list of salutations easily. To add a new salutation, just add a row to the table, and all the scripts that use the table automatically adjust to the change with no additional programming. You can also rename or delete values from the lookup table with no need to update your scripts.

Lookup tables can be used to present enumeration-type fields even for columns that you may not represent using an ENUM. Earlier, we discussed the possibility of converting the qty column in the coffee table from a TINYINT to an ENUM, to allow a specific set of quantity values to be listed explicitly in the column specification and converted automatically to a list element such as a pop-up menu. Another way to address this issue is to continue representing qty as a TINYINT, but list the valid quantities in a separate lookup table.

Using Lookup Tables for Form Generation

Let s see how lookup tables work for generating form elements by writing a script, coffee2.pl, for entering coffee orders that is similar to coffee.pl but that uses lookup tables qty_value and size_value for the qty and size fields. The table for quantity values can be very simple, because it needs only one column. To create the table and initialize it, execute the following statements:

 CREATE TABLE qty_value  (     value   TINYINT UNSIGNED NOT NULL  )  INSERT INTO qty_value (value) VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10) 

The INSERT statement creates rows with values from 1 to 10. It uses the syntax that allows multiple VALUES() lists, so that you can insert multiple rows with a single statement. If your version of MySQL is older than 3.22.5, you ll need to insert the values one at a time:

 INSERT INTO qty_value (value) VALUES(1)  INSERT INTO qty_value (value) VALUES(2)  INSERT INTO qty_value (value) VALUES(3)  ... 

After you have the qty_value table initialized, the quantity values can be selected from it using a simple query:

 SELECT value FROM qty_value ORDER BY value 

If we assume the existence of a get_lookup_values() function that takes a database handle and a query as arguments and returns the looked-up values, we can generate a pop-up menu for the qty field as follows:

 $qty_val_ref = get_lookup_values (                         $dbh,                          "SELECT value FROM qty_value ORDER BY value");  print popup_menu (-name => "qty", -values => $qty_val_ref); 

Note the ORDERBY clause in the query. The function can t possibly read your mind to know how to sort the values, so it s up to you to specify what the order is. get_lookup_values() returns a reference to an array that can be passed to popup_menu() to provide the list of values displayed in the menu.

For the serving size lookup table, one possibility is to create and initialize a size_value table that lists the valid sizes like this:

 CREATE TABLE size_value  (     value CHAR(3) NOT NULL  )  INSERT INTO size_value (value) VALUES('S'),('M'),('L') 

However, this table actually is not very useful, because there is no natural way to sort the rows into the proper order (S, M, L). We can t just omit the ORDERBY clause when we select the rows from the table and hope that they come out in the right order, but ORDER BY value won t return the rows in correct order, either.^[6] To deal with this, we ll add a sequence number column that enables you to specify the value order explicitly. In addition, a third column containing a more descriptive label for each size will enable you to generate a couple of different styles of lookup lists. Here are the resulting table-creation and table-initialization statements:

^[6] You may object that if you add DESC to sort the column in reverse (ORDER BY value DESC) the values do indeed sort into the order S, M, L, but that s just coincidence. (Add a row containing XL to the size_value table and see what happens.)

 CREATE TABLE size_value  (     seq     TINYINT UNSIGNED NOT NULL PRIMARY KEY,      value   CHAR(3) NOT NULL,      label   CHAR(6) NOT NULL  )  INSERT INTO size_value (seq,value,label)      VALUES(1,'S','Small'),(2,'M','Medium'),(3,'L','Large') 

With this version of the size_value table, we can get the size values in the proper order and use them to generate radio buttons like this:

 $size_val_ref = get_lookup_values (                         $dbh,                          "SELECT value FROM size_value ORDER BY seq");  print radio_group (-name => "size", -values => $size_val_ref); 

Furthermore, if we write get_lookup_values() to be smart enough, it can return not only a reference to the list of field values, but a reference to a map for associating values with the labels. This allows a different style of labels to be presented. For example, we could call it like this for the serving sizes to be able to display labels like Small, Medium, and Large rather than S, M, and L :

 ($size_val_ref, $size_label_ref) = get_lookup_values (                         $dbh,                          "SELECT value, label FROM size_value ORDER BY seq");  print radio_group (-name => "size",                      -values => $size_val_ref,                      -labels => $size_label_ref); 

Note the differences from the way we previously generated the size element in the form. We re now passing to get_lookup_values() a query that retrieves two columns (value and label) rather than one, and we re getting back two references as a result. The first points to the list of values, as before; the second points to a value-to-label hash that we can pass as a labels parameter to radio_group().

This illustrates something you can do with lookup tables easily that you can t do at all when you generate form elements by reading table descriptions. The description for an ENUM column lists the values of the column, and you can use that to create a form element. If you want to associate particular labels with the values, however, that isn t possible using the column description alone.

Of course, we still have to write get_lookup_values(), and we have to enable it to know whether to construct the value-to-label map. That s actually not very difficult, because when DBI executes a query, it can tell how many columns are in the result set. If there is a second column, we ll assume it contains labels and use it to construct a value-to-label map:

 sub get_lookup_values  { my ($dbh, $query) = @_;  my ($sth, @val, $label_ref);      $sth = $dbh->prepare ($query);      $sth->execute ();      # check whether or not query returns a label column      $label_ref = {} if $sth->{NUM_OF_FIELDS} > 1;      while (my @row = $sth->fetchrow_array ())      {         push (@val, $row[0]);          $label_ref->{$row[0]} = $row[1] if $$label_ref;  # add label map entry      }      $sth->finish ();      return (wantarray () ? (\@val, $label_ref) : \@val);  } 

You can see that although it takes a little more work to create a lookup table, you gain some flexibility in terms of the kinds of displays you can generate. This approach offers a number of possibilities you can exploit. For example, it can help you generate forms in different languages. If you add a column indicating the language of a label, you can store rows for several different languages in a lookup table and retrieve the rows for the appropriate language at form-generation time.

Now that we have our lookup tables set up, one more thing we ll do for coffee2.pl is to create a new table, coffee2. It s identical to the coffee table except that the size column is a VARCHAR rather than an ENUM :

 CREATE TABLE coffee2  (     id      INT NOT NULL AUTO_INCREMENT PRIMARY KEY, # record ID/timestamp      ts      TIMESTAMP,      qty     TINYINT UNSIGNED NOT NULL DEFAULT 1,     # quantity      size    VARCHAR(10) NOT NULL DEFAULT 'M',        # serving size      extras  SET('cream','sugar'),                    # extra ingredients      remark  VARCHAR(30)                              # special instructions  ) 

Why this difference between coffee and coffee2 ? Because we re listing the possible serving sizes in the lookup table size_values. There s no need to list them again in the coffee record table, and in fact doing so may cause you problems. For our initial version of s ize_value, the only sizes it will contain are S, M, and L, which matches the sizes we ve been using all along. If you add, modify, or delete values from size_value, however, you d create a mismatch between an ENUM version of the size column and the values in the lookup table. This problem goes away if we use a size column that is a simple character string.

We still need to consider the problem of getting the field defaults initialized. That really isn t much of a problem, because we can get them from the coffee2 table description and load them into the parameter environment like this:

 $tbl_info = WebDB::TableInfo->get ($dbh, "coffee2");  param (-name => "qty", -value => $tbl_info->default ("qty"));  param (-name => "size", -value => $tbl_info->default ("size")); 

Putting this all together, we can generate the entry form using lookup tables like this:

 sub display_form  { my ($dbh, $tbl_info, $qty_val_ref, $size_val_ref, $size_label_ref, $remark_len);  my (@extras, @extras_default);      $dbh = WebDB::connect ();                           # connect to database      $tbl_info = WebDB::TableInfo->get ($dbh, "coffee2");# table information      # Look up values for qty field      $qty_val_ref = get_lookup_values (                         $dbh,                          "SELECT value FROM qty_value ORDER BY value");      # Look up values and labels for size field      ($size_val_ref, $size_label_ref) = get_lookup_values (                         $dbh,                          "SELECT value, label FROM size_value ORDER BY seq");      $dbh->disconnect ();                                # done with database      # set defaults for qty and size fields      param (-name => "qty", -value => $tbl_info->default ("qty"));      param (-name => "size", -value => $tbl_info->default ("size"));      # ditto for extras field      @extras_default = $tbl_info->default ("extras");      param (-name => "extras", -value => \@extras_default);      # get length for remark field, value for extras field      $remark_len = $tbl_info->length ("remark");      @extras = $tbl_info->members ("extras");      print start_form (-action => url ()),          p ("Quantity:"),          popup_menu (-name => "qty", -values => $qty_val_ref),          p ("Serving size:"),          radio_group (-name => "size",                      -values => $size_val_ref,                      -labels => $size_label_ref),          p ("Extras:"),          checkbox_group (-name => "extras", -values => \@extras),          p ("Special instructions:"),          textfield (-name => "remark", -size => $remark_len),          br (), br (),          submit (-name => "choice", -value => "Submit"),          end_form ();  } 

Another way to specify default values is to store them in the lookup table itself. First, use ALTER TABLE to add a defval column to the qty_value and size_value tables:

 ALTER TABLE qty_value ADD defval TINYINT DEFAULT 0  ALTER TABLE size_value ADD defval TINYINT DEFAULT 0 

Then specify the initial default values with these two statements:

 UPDATE qty_value SET defval = 1 WHERE value = 1  UPDATE size_value SET defval = 1 WHERE value = 'M' 

Note that the defval column doesn t contain the default value; it indicates which row contains the default value. In the size_value table, for example, all rows have a defval value of 0, except the row where the value column is M, which has defval set to 1.

To use the defaults within our script, we read them and load them into the parameter environment like so:

 $qty_default = get_lookup_default ($dbh, "qty_value", "value", "defval");  $size_default = get_lookup_default ($dbh, "size_value", "value", "defval");  param (-name => "qty", -value => $qty_default);  param (-name => "size", -value => $size_default); 

The get_lookup_default() function used here looks in the given table and extracts the value of the specified column for the row where value of the default value column is 1 :

 sub get_lookup_default  { my ($dbh, $tbl_name, $col_name, $def_col_name) = @_;  my $default;      $default = $dbh->selectrow_array (                 "SELECT $col_name FROM $tbl_name WHERE $def_col_name = 1");      return ($default);  } 

This approach makes the default value explicit in the lookup table. You can also change the default just by modifying the contents of the table. Suppose you want to change the serving size default from M to S. Here s one way to do it:

 UPDATE size_value SET defval = 0 WHERE value = 'M'  UPDATE size_value SET defval = 1 WHERE value = 'S' 

Or you can accomplish the same end with a single statement:

 UPDATE size_value SET defval = IF(value='S',1,0) 

MySQL evaluates the expression specified in the first argument of the IF() function. If the expression is true (that is, not 0 or NULL), the value of IF() becomes the value of the second argument. If the expression is false, IF() returns the third argument. The result is to set defval to 1 for the row with S in the value column, and to 0 for all other rows.

One limitation to be aware of when you store the default value indicator in the lookup table is that this approach doesn t work if the table is shared among different applications that want to have different default values.

Using Lookup Tables for Validation

When a user submits a form containing an element that was generated by means of a lookup table, you can also use the table to validate the element. The following function, check_lookup_value(), runs a query to determine whether a particular value can be found in a lookup table. It returns true if the value is present, and false otherwise:

 sub check_lookup_value  { my ($dbh, $tbl_name, $col_name, $val) = @_;  my $result;      $result = $dbh->selectrow_array (                 "SELECT COUNT(*) FROM $tbl_name WHERE $col_name = ?",                      undef, $val);      return ($result > 0);  } 

You might use the function like this to perform field validation:

 push (@errors, "Quantity is illegal")      unless check_lookup_value ($dbh, "qty_value", "value", param ("qty"));  push (@errors, "Serving size is illegal")      unless check_lookup_value ($dbh, "size_value", "value", param ("size")); 

Other Ideas for Using Lookup Tables

Enumeration list lookup tables have myriad uses in data entry applications:

• Keep a list of state abbreviations and names in a table and use it for presenting the state field in forms that collect address information. Sometimes you can use it multiple times in the same form, such as when you collect both a shipping address and a billing address.

• Forms that enable users to indicate the specifications for build-to-order items are a natural application for lookup tables. Every part of the specification for which multiple choices exist can be represented by a list element constructed from the contents of a lookup table. If you re selling computers, for example, you can use lookup tables to present the options for processor speed, hard drive capacity, amount of memory, and so forth.

• A lookup table can help you in any situation involving a primary underlying table in which you store a code value for efficiency, but for which you have a secondary table that lists more meaningful labels for the codes. If you re collecting medical records for surgeries that are stored by code number, use a lookup table to present a descriptive string for each kind of surgery to the user, and then map the string to the code when storing the record. If you have a department table that lists department titles and codes, use it to present a list of titles to users for a personnel entry form application that stores codes in the personnel table.

Lookup tables are useful in search applications, too. For real estate offerings, you can present a list of property types (such as retail, office, industrial, residential), locations, and so forth. For inventory searches, you can allow the user to limit the search by selecting a particular item category. For example, if you have listings of furniture items, you can present a category pop-up menu containing items such as dining area, kitchen, living room, and bedroom.

If you re using a lookup table to allow you to display specific labels to users in an entry or editing form, you may also want to use the table later for purposes such as report generation. Let s say you want to create a summary that shows your coffee sales per month, categorized by serving size. A basic query that displays sizes as S, M, and L is as follows:

 mysql> SELECT YEAR(ts) AS year, MONTH(ts) AS month, size,      -> SUM(qty) AS quantity      -> FROM coffee2      -> GROUP BY year, month, size;  +------+-------+------+----------+  | year | month | size | quantity |  +------+-------+------+----------+  | 2001 |     1 | L    |      118 |  | 2001 |     1 | M    |      197 |  | 2001 |     1 | S    |       74 |  | 2001 |     2 | L    |      173 |  | 2001 |     2 | M    |      229 |  | 2001 |     2 | S    |       82 |  | 2001 |     3 | L    |      198 |  | 2001 |     3 | M    |      243 |  | 2001 |     3 | S    |      125 |  +------+-------+------+----------+ 

However, you can display the more meaningful labels found in the size_value table by using a join:

 mysql> SELECT YEAR(c.ts) AS year, MONTH(c.ts) AS month, s.label AS size,      -> SUM(c.qty) AS quantity      -> FROM coffee2 AS c, size_value AS s      -> WHERE c.size = s.value      -> GROUP BY year, month, size      -> ORDER BY year, month, s.seq;  +------+-------+--------+----------+  | year | month | size   | quantity |  +------+-------+--------+----------+  | 2001 |     1 | Small  |       74 |  | 2001 |     1 | Medium |      197 |  | 2001 |     1 | Large  |      118 |  | 2001 |     2 | Small  |       82 |  | 2001 |     2 | Medium |      229 |  | 2001 |     2 | Large  |      173 |  | 2001 |     3 | Small  |      125 |  | 2001 |     3 | Medium |      243 |  | 2001 |     3 | Large  |      198 |  +------+-------+--------+----------+ 

Note how this second query takes advantage of the seq column in the lookup table so that you can sort size values properly. (If you have a GROUP BY clause in a query, the output normally is sorted on the columns named in that clause. The second query needs to sort on a different set of columns, necessitating an explicit ORDER BY clause that includes the seq column.)

Using Lookup Tables for Sets

You may be curious why we converted form generation in coffee2.pl to use lookup tables for the qty and size fields, but not for the extras field. The reason is that the underlying extras column in the database table is a SET. Recall that we converted the size column from an ENUM to a VARCHAR column because an ENUM would go out of correspondence with the rows in the size_value table if we added, changed, or deleted rows in that table. Similarly, if you want to use a lookup table for the extras field, you d have to change the extras column to a VARCHAR. In this case, however, there s an additional issue. Unlike the size field, for which only one value can be chosen, it s allowable to select several extras values. This means the extras column would have to be wide enough to accommodate a string listing all the values if they are all selected. Currently the longest value would be ' cream,sugar '; but if you started adding other options such as chocolate syrup, sprinkles, and whipped cream, you could end up needing a rather long column.

To convert a SET column to use a lookup table, you really should use a different approach, outlined here:

• Create the lookup table extras_value and put one row in it for each applicable value. Probably you d want a seq column (as with size_value) so that you can sort the rows into a specific order:

   CREATE TABLE extras_value  (     seq     TINYINT UNSIGNED NOT NULL PRIMARY KEY,      value   CHAR(10) NOT NULL  )  INSERT INTO extras_value (seq,value) VALUES(1,'cream'),(2,'sugar') 

• Create a table, coffee3, that is like coffee2 except that it has no extras column at all:

   CREATE TABLE coffee3  (     id      INT NOT NULL AUTO_INCREMENT PRIMARY KEY, # record ID/timestamp      ts      TIMESTAMP,      qty     TINYINT UNSIGNED NOT NULL DEFAULT 1,     # quantity      size    VARCHAR(10) NOT NULL DEFAULT 'M',        # serving size      remark  VARCHAR(30)                              # special instructions  ) 

• Create another table, extras, with an id column for linking the record to the parent record in the coffee2 table, and a value column declared the same way as the value column in the extras_value table:

   CREATE TABLE extras  (     id      INT NOT NULL,                # parent record ID      INDEX   (id),      value   VARCHAR(10)  ) 

• When you store an order, create one row in the extras table for each selected extra ingredient. Each row should be given the same id value as the corresponding row in the coffee2 table.

only for RuBoard - do not distribute or recompile