Creating Single-Pick Form Elements from Database Content

18.3.1 Problem

A form needs to present a field that offers the user a set of options but allows only one of them to be selected.

18.3.2 Solution

Use a single-pick list element. These include radio button sets, pop-up menus, and scrolling lists.

18.3.3 Discussion

Single-pick form elements allow you to present multiple choices from which a single option can be selected. Our example involves several sets of single-pick choices:

• The list of colors in the cow_color table. These can be obtained with the following query:

  mysql> SELECT color FROM cow_color ORDER BY color; +---------------+ | color | +---------------+ | Black | | Black & White | | Brown | | Cream | | Red | | Red & White | | See-Through | +---------------+

  Note that some of the colors contain a & character, which is special in HTML. This means they will need HTML-encoding when placed into list elements. (Actually, we'll perform encoding for all the list elements in the form, but these values illustrate why it's a good idea to get in that habit.)

• The list of legal figurine sizes in the size column of the cow_order table. The column is represented as an ENUM, so the possible values and the default value can be obtained using SHOW COLUMNS:

  mysql> SHOW COLUMNS FROM cow_order LIKE 'size'G *************************** 1. row *************************** Field: size Type: enum('small','medium','large') Null: YES Key: Default: medium Extra:

• The list of state names and abbreviations. These are available from the states table:

  mysql> SELECT abbrev, name FROM states ORDER BY name; +--------+----------------+ | abbrev | name | +--------+----------------+ | AL | Alabama | | AK | Alaska | | AZ | Arizona | | AR | Arkansas | | CA | California | | CO | Colorado | | CT | Connecticut | ...

The number of choices varies for each of these lists. As shown, there are 3 sizes, 7 colors, and 50 states. The size values are best represented as a set of radio buttons or a pop-up menu; a scrolling list is unnecessary because the number of choices is small. The set of colors can reasonably be displayed using any of the single-pick element types; it's small enough that a set of radio buttons wouldn't take a lot of space, but large enough that you may want to allow scrollingparticularly if you make additional colors available. The list of states is likely to have more items than you'd want to present as a set of radio buttons, so it's most suitable for presentation as a pop-up menu or scrolling list.

I will discuss the HTML syntax for these types of elements, then show how to generate them from within scripts.

• Radio buttons.

  A group of radio buttons consists of elements of type radio, all with the same name attribute. Each element also includes a value attribute. A label to display can be given after the tag. To mark an item as the default initial selection, add a checked attribute. The following radio button group displays the possible cow figurine sizes, using checked to mark medium as the initially selected value:

  small medium large

• Pop-up menus

  A pop-up menu is a list enclosed within and tags, with each item in the menu enclosed within

  and tags. Each element has a value attribute, and its body provides a label to be displayed. To indicate a default selection, add a selected attribute to the appropriate item. If no item is so marked, the first item becomes the default, as is the case for the following pop-up menu:

  BlackBlack & WhiteBrownCreamRedRed & WhiteSee-Through

• Scrolling lists

  A scrolling list is displayed as a set of items in a box. The list may contain more items than are visible in the box, in which case the browser displays a scrollbar that the user can use to bring the other items into view. The HTML syntax for scrolling lists is similar to that for pop-up menus, except that the opening tag includes a size attribute indicating how many rows of the list should be visible in the box. By default, a scrolling list is a single-pick element; Recipe 18.4 discusses how to allow multiple picks. The following single-pick scrolling list includes an item for each U.S. state, of which six will be visible at a time: AlabamaAlaskaArizonaArkansasCalifornia ...West VirginiaWisconsinWyoming

These list elements all have several things in common:

• A name for the element. When the user submits the form, the browser associates this name with whatever value the user selected.
• A set of values, one for each item in the list. These determine the values that are available for selection.
• An optional default value that determines which item in the list is selected initially when the browser displays the list.
• A set of labels, one for each item. These determine what the user sees when the form is displayed, but are discarded when the form is submitted.

To produce a list element for a form using database content, issue a query to select the appropriate values and labels, encode any special characters they contain, and add the HTML tags that are appropriate for the kind of list you want to display. Should you wish to indicate a default selection, add a checked or selected attribute to the proper item in the list.

Let's consider how to produce form elements for the color and state lists first, both of which are produced by fetching a set of rows from a table.

In JSP, you can display a set of radio buttons for the colors using JSTL tags as follows. The color names are used as both the values and the labels, so you print them twice:

SELECT color FROM cow_color ORDER BY color " />

performs HTML entity encoding, so the & character that is present in some of the color values will be converted to & automatically and will not cause display problems in the resulting web page.

To display a pop-up menu instead, the query is the same, but you change the row-fetching loop:

SELECT color FROM cow_color ORDER BY color ">

The pop-up menu can be changed easily to a scrolling list; just add a size attribute to the opening tag. For example, to make three colors visible at a time, generate the list like this: SELECT color FROM cow_color ORDER BY color ">

Generating a list element for the set of states is similar, except that the labels are not the same as the values. To make the labels more meaningful to customers, display the full state names. But the value that is returned when the form is submitted should be an abbreviation, because that is what gets stored in the cow_order table. To produce a list that way, select both the abbreviations and the full names, then insert them into the proper parts of each list item. For example, to create a pop-up menu, do this:

SELECT abbrev, name FROM states ORDER BY name ">

These JSP examples use an approach that prints each list item individually. List element generation in CGI.pm-based Perl scripts proceeds on a different basis: extract the information from the database first, then pass it all to a function that returns a string representing the form element. The functions that generate single-pick elements are radio_group( ), popup_menu( ), and scrolling_list( ). These have several arguments in common:

name

Indicates what you want to call the element.

values

Specifies the values for the items in the list. This should be a reference to an array.

default

Indicates the initially selected item in the element. This argument is optional. For a radio button set, CGI.pm automatically selects the first button by default if this argument is missing. To defeat that behavior, provide a default value that is not present in the values list. (This value cannot be undef or the empty string.)

labels

Provides the labels to associate with each value. This argument is optional; if it's missing, CGI.pm uses the values as the labels. Otherwise, the labels argument should be a reference to a hash that associates each value with its corresponding label. For example, to produce a list element for cow colors, the values and labels are the same, so no labels argument is necessary. However, to produce a state list, labels will be a reference to a hash that maps each state abbreviation to its full name.

Some of the functions take additional arguments. For radio_group( ), you can supply a linebreak argument to specify that the buttons should be displayed vertically rather than horizontally. scrolling_list( ) takes a size argument indicating how many items should be visible at a time. (The CGI.pm documentation describes additional arguments that are not used here at all. For example, there are arguments for laying out radio buttons in tabular form, but I'm not going to be that fancy.)

To construct a form element using the colors in the cow_color table, we need to retrieve them into an array:

my $color_ref = $dbh->selectcol_arrayref ( "SELECT color FROM cow_color ORDER BY color");

selectcol_arrayref( ) returns an array reference, which could if necessary be coerced to an array like this:

my @colors = @{$color_ref};

But the values argument for the CGI.pm functions that create list elements should be a reference anyway, so we'll just use $color_ref as is. To create a group of radio buttons, a pop-up menu, or a single-pick scrolling list, invoke the functions as follows:

print radio_group (-name => "color", -values => $color_ref, -linebreak => 1); # display buttons vertically print popup_menu (-name => "color", -values => $color_ref); print scrolling_list (-name => "color", -values => $color_ref, -size => 3); # display 3 items at a time

The values and the labels for the color list are the same, so no labels argument need be given; CGI.pm will use the values as labels by default. Note that we haven't HTML-encoded the colors here, even though some of them contain a & character. CGI.pm functions for generating form elements automatically perform HTML-encoding, unlike its functions for creating non-form elements.

To produce a list of states for which the values are abbreviations and the labels are full names, we do need a labels argument. It should be a reference to a hash that maps each value to the corresponding label. Construct the value list and label hash as follows:

my @state_values; my %state_labels; my $sth = $dbh->prepare ("SELECT abbrev, name FROM states ORDER BY name"); $sth->execute ( ); while (my ($abbrev, $name) = $sth->fetchrow_array ( )) { push (@state_values, $abbrev); # save each value in an array $state_labels{$abbrev} = $name; # map each value to its label }

Pass the resulting list and hash by reference to popup_menu( ) or scrolling_list( ), depending on which kind of list element you want to produce:

print popup_menu (-name => "state", -values => @state_values, -labels => \%state_labels); print scrolling_list (-name => "state", -values => @state_values, -labels => \%state_labels, -size => 6); # display 6 items at a time

If you're using an API that doesn't provide a ready-made set of functions for producing form elements the way CGI.pm does, you may elect either to print HTML as you fetch list items from MySQL, or write utility routines that generate the form elements for you. The following discussion considers how to implement both approaches, using PHP and Python.

In PHP, the list of values from the cow_color table can be presented in a pop-up as follows using a fetch-and-print loop like this:

$query = "SELECT color FROM cow_color ORDER BY color"; $result_id = mysql_query ($query, $conn_id); print (" "); while (list ($color) = mysql_fetch_row ($result_id)) { $color = htmlspecialchars ($color); print ("$color "); } mysql_free_result ($result_id); print (" ");

Python code to do the same is similar:

query = "SELECT color FROM cow_color ORDER BY color" cursor = conn.cursor ( ) cursor.execute (query) print "" for (color, ) in cursor.fetchall ( ): color = cgi.escape (color, 1) print "%s" % (color, color) cursor.close ( ) print ""

The state list requires different values and labels, so the code is slightly more complex. In PHP, it looks like this:

$query = "SELECT abbrev, name FROM states ORDER BY name"; $result_id = mysql_query ($query, $conn_id); print (" "); while (list ($abbrev, $name) = mysql_fetch_row ($result_id)) { $abbrev = htmlspecialchars ($abbrev); $name = htmlspecialchars ($name); print ("$name "); } mysql_free_result ($result_id); print (" ");

And in Python, like this:

query = "SELECT abbrev, name FROM states ORDER BY name" cursor = conn.cursor ( ) cursor.execute (query) print "" for (abbrev, name) in cursor.fetchall ( ): abbrev = cgi.escape (abbrev, 1) name = cgi.escape (name, 1) print "%s" % (abbrev, name) cursor.close ( ) print ""

Radio buttons and scrolling lists can be produced in similar fashion. But rather than doing so, let's try a different approach and construct a set of functions that generate form elements, given the proper information. The functions return a string representing the appropriate kind of form element, and are invoked with the following arguments:

make_radio_group (name, values, labels, default, vertical) make_popup_menu (name, values, labels, default) make_scrolling_list (name, values, labels, default, size, multiple)

These functions have several arguments in common:

name

Indicates the name of the form element.

values

An array or list of values for the items in the element.

labels

Another array that provides the corresponding element label to display for each value. The two arrays must be the same size. (If you want to use the values as the labels, just pass the same array to the function twice.)

default

Indicates the initial value of the form element. This should be a scalar value, except for make_scrolling_list( ). We'll write that function to handle either single-pick or multiple-pick lists (and use it for the latter purpose in Recipe 18.4), so its default value is allowed to be either a scalar or an array. If there is no default, pass a value that isn't contained in the values array; typically, an empty string will do.

Some of the functions also have additional arguments that apply only to particular element types:

• vertical applies to radio button groups. If true, it indicates that the items should be stacked vertically rather than horizontally.
• The size and multiple arguments apply to scrolling lists. size indicates how many items in the list are visible, and multiple should be true if the list allows multiple selections.

The implementation of some of these list-generating functions is discussed here, but you can find the code for all of them in the lib directory of the recipes distribution. All of them act like CGI.pm for form element functions in the sense that they automatically perform HTML-encoding on argument values that are incorporated into the list.

In PHP, the make_radio_group( ) function for creating a set of radio buttons can be written like this:

function make_radio_group ($name, $values, $labels, $default, $vertical) { if (!is_array ($values)) return ("make_radio_group: values argument must be an array"); if (!is_array ($labels)) return ("make_radio_group: labels argument must be an array"); if (count ($values) != count ($labels)) return ("make_radio_group: value and label list size mismatch"); $str = ""; for ($i = 0; $i < count ($values); $i++) { # select the item if it corresponds to the default value $checked = ($values[$i] == $default ? " checked="checked"" : ""); $str .= sprintf ( "%s", htmlspecialchars ($name), htmlspecialchars ($values[$i]), $checked, htmlspecialchars ($labels[$i])); if ($vertical) $str .= " "; # display items vertically $str .= " "; } return ($str); }

The function performs some preliminary argument checking, then constructs the form element as a string, which it returns. To use the function to present cow colors, invoke it after fetching the items from the cow_color table as follows:

$values = array ( ); $query = "SELECT color FROM cow_color ORDER BY color"; $result_id = mysql_query ($query, $conn_id); if ($result_id) { while (list ($color) = mysql_fetch_row ($result_id)) $values[ ] = $color; mysql_free_result ($result_id); } print (make_radio_group ("color", $values, $values, "", TRUE));

The $values array is passed to the function twice because it's used for both the values and the labels.

If you want to present a pop-up menu, use the following function instead:

function make_popup_menu ($name, $values, $labels, $default) { if (!is_array ($values)) return ("make_popup_menu: values argument must be an array"); if (!is_array ($labels)) return ("make_popup_menu: labels argument must be an array"); if (count ($values) != count ($labels)) return ("make_popup_menu: value and label list size mismatch"); $str = ""; for ($i = 0; $i < count ($values); $i++) { # select the item if it corresponds to the default value $checked = ($values[$i] == $default ? " selected="selected"" : ""); $str .= sprintf ( "

%s

", htmlspecialchars ($values[$i]), $checked, htmlspecialchars ($labels[$i])); } $str = sprintf ( " %s ", htmlspecialchars ($name), $str); return ($str); }

make_popup_menu( ) has no $vertical parameter, but otherwise you invoke it the same way as make_radio_group( ):

print (make_popup_menu ("color", $values, $values, ""));

The make_scrolling_list( ) function is similar to make_popup_menu( ), so I won't show its implementation here. To invoke it to produce a single-pick list, pass the same arguments as for make_popup_menu( ), but indicate how many rows should be visible at once, and add a multiple argument of FALSE:

print (make_scrolling_list ("color", $values, $values, "", 3, FALSE));

The state list uses different values and labels. Fetch them like this:

$values = array ( ); $labels = array ( ); $query = "SELECT abbrev, name FROM states ORDER BY name"; $result_id = mysql_query ($query, $conn_id); if ($result_id) { while (list ($abbrev, $name) = mysql_fetch_row ($result_id)) { $values[ ] = $abbrev; $labels[ ] = $name; } mysql_free_result ($result_id); }

Then use the values and labels to produce the type of list you want:

print (make_popup_menu ("state", $values, $labels, "")); print (make_scrolling_list ("state", $values, $labels, "", 6, FALSE));

Python implementations of the utility functions are similar to the PHP versions. For example, make_popup_menu( ) looks like this:

def make_popup_menu (name, values, labels, default): if type (values) not in (types.ListType, types.TupleType): return ("make_popup_group: values argument must be a list") if type (labels) not in (types.ListType, types.TupleType): return ("make_popup_group: labels argument must be a list") if len (values) != len (labels): return ("make_popup_group: value and label list size mismatch") str = "" for i in range (len (values)): value = values[i] label = labels[i] # make sure value and label are strings if type (value) is not types.StringType: value = `value` if type (label) is not types.StringType: label = `label` # select the item if it corresponds to the default value if type (default) is not types.StringType: default = `default` if value == default: checked = " selected="selected"" else: checked = "" str = str + "

%s

" % (cgi.escape (value, 1), checked, cgi.escape (label, 1)) if type (name) is not types.StringType: name = `name` str = " %s " % (cgi.escape (name, 1), str) return (str)

To present the cow colors in a form, fetch them like this:

values = [ ] query = "SELECT color FROM cow_color ORDER BY color" cursor = conn.cursor ( ) cursor.execute (query) for (color, ) in cursor.fetchall ( ): values.append (color) cursor.close ( )

Then convert the list to a form element as follows:

print make_radio_group ("color", values, values, "", 1) print make_popup_menu ("color", values, values, "") print make_scrolling_list ("color", values, values, "", 3, 0)

To present the state list, fetch the names and abbreviations:

values = [ ] labels = [ ] query = "SELECT abbrev, name FROM states ORDER BY name" cursor = conn.cursor ( ) cursor.execute (query) for (abbrev, name) in cursor.fetchall ( ): values.append (abbrev) labels.append (name) cursor.close ( )

Then pass them to the appropriate function:

print make_popup_menu ("state", values, labels, "") print make_scrolling_list ("state", values, labels, "", 6, 0)

One thing the Python functions do that their PHP counterparts do not is to explicitly convert argument values that get incorporated into the list to string form. This is necessary because cgi.escape( ) raises an exception if you try to use it to HTML-encode a non-string value.

We have thus far considered how to fetch rows from the cow_color and states tables and convert them to form elements. Another element that needs to be part of the form for the online cow-ordering application is the field for specifying cow figurine size. The legal values for this field come from the size column in the cow_order table. That column is an ENUM, so getting the legal values for the corresponding form element is a matter of getting the column definition and parsing it apart.

Fortunately, a lot of the work involved in this task has already been done in Recipe 9.7, which develops utility routines to get ENUM or SET column metadata. In Perl, for example, invoke the get_enumorset_info( ) function as follows to get the size column metadata:

my $size_info = get_enumorset_info ($dbh, "cow_order", "size");

The resulting $size_info value is a reference to a hash that has several members, two of which are relevant to our purposes here:

$size_info->{values} $size_info->{default}

The values member is a reference to a list of the legal enumeration values, and default is the column's default value. This information is in a format that can be converted directly to a form element such as a group of radio buttons or a pop-up menu as follows:

print radio_group (-name => "size", -values => $size_info->{values}, -default => $size_info->{default}, -linebreak => 1); # display buttons vertically print popup_menu (-name => "size", -values => $size_info->{values}, -default => $size_info->{default});

The default value is medium, so that's the value that will be selected initially when the browser displays the form.

The equivalent metadata-fetching function for PHP returns an associative array. Use it like this to generate form elements from the size column metadata:

$size_info = get_enumorset_info ($conn_id, "cow_order", "size"); print (make_radio_group ("size", $size_info["values"], $size_info["values"], $size_info["default"], TRUE)); # display items vertically print (make_popup_menu ("size", $size_info["values"], $size_info["values"], $size_info["default"]));

The Python version of the function returns a dictionary, which is used similarly:

size_info = get_enumorset_info (conn, "cow_order", "size") print make_radio_group ("size", size_info["values"], size_info["values"], size_info["default"], 1) print make_popup_menu ("size", size_info["values"], size_info["values"], size_info["default"])

When you use ENUM values like this to create list elements, the values are displayed in the order they are listed in the column definition. The size column definition lists the values in the proper display order (small, medium, large), but for columns for which you want a different order, sort the values appropriately.

To demonstrate how to process column metadata to generate form elements in JSP pages, I'm going to use a function embedded into the page. A better approach would be to write a custom action in a tag library that maps onto a class that returns the information, but custom tag writing is beyond the scope of this book. The examples take the following approach instead:

• Use the JSTL tags to execute a SHOW COLUMNS query to get the ENUM column definition, then move the definition into page context.
• Write a function that extracts the definition from page context, parses it into an array of individual enumeration values, and moves the array back into page context.
• Access the array using a JSTL iterator that displays each of its values as a list item. For each value, compare it to the column's default value and mark it as the initially selected item if it's the same.

The function that extracts legal values from an ENUM or SET column definition is named getEnumOrSetValues( ). Place it into a JSP page like this:[2]

[2] The getEnumOrSetValues( ) function requires the Jakarta ORO regular expression library, which can be obtained at the Jakarta site, http://jakarta.apache.org. Copy its JAR file to Tomcat's common/lib directory and restart Tomcat to make the library available to your JSP pages.

<%@ page import="java.util.*" %> <%@ page import="org.apache.oro.text.perl.*" %> <%! // declare a class method for busting up ENUM/SET values. // typeDefAttr - the name of the page context attribute that contains // the columm type definition // valListAttr - the name of the page context attribute to stuff the // column value list into void getEnumOrSetValues (PageContext ctx, String typeDefAttr, String valListAttr) { Perl5Util util = new Perl5Util ( ); String typeDef = ctx.getAttribute (typeDefAttr).toString ( ); // strip off leading "enum(" and trailing ")", leaving // comma-separated list of quoted values String qValStr = util.substitute ("s/^(enum|set)\((.*)\)$/$2/", typeDef); List quotedVal = new ArrayList ( ); List unquotedVal = new ArrayList ( ); // split string at commas to produce list of quoted values util.split (quotedVal, "/,/", qValStr); for (int i = 0; i < quotedVal.size ( ); i++) { // strip quotes from each value String s = quotedVal.get (i).toString ( ); s = util.substitute ("s/^'(.*)'$/$1/g", s); unquotedVal.add (s); } ctx.setAttribute (valListAttr, unquotedVal); } %>

The function takes three arguments:

• The page context object.
• The name of the page attribute that contains the column definition. This is the function "input."
• The name of the page attribute into which to place the resulting array of legal column values. This is the function "output."

To generate a list element from the size column, begin by fetching the column metadata: extract the column value list into a JSTL variable named values and the default value into a variable named default as follows:

SHOW COLUMNS FROM cow_order LIKE 'size' <% getEnumOrSetValues (pageContext, "typeDef", "values"); %>

Then use the value list and default value to construct a form element. For example, produce a set of radio buttons like this:

" checked="checked" />

Or a pop-up menu like this:

" selected="selected" >

The list-generating methods discussed here are not tied to any particular database table, so they can be used to create form elements for all kinds of data, not just those shown for the cow-ordering scenario. For example, to allow a user to pick a table name in a database administration application, you can generate a scrolling list containing an item for each table in the database. A CGI.pm-based script might do so like this:

my $table_ref = $dbh->selectcol_arrayref ("SHOW TABLES"); print scrolling_list (-name => "table", -values => $table_ref, -size => 10); # display 10 items at a time

Query results need not necessarily even be related to database tables. For example, if you want to present a list with an entry for each of the last seven days from within a JSP page, you can calculate the dates using this query:

SELECT DATE_SUB(CURDATE( ),INTERVAL 5 DAY), DATE_SUB(CURDATE( ),INTERVAL 4 DAY), DATE_SUB(CURDATE( ),INTERVAL 3 DAY), DATE_SUB(CURDATE( ),INTERVAL 2 DAY), DATE_SUB(CURDATE( ),INTERVAL 1 DAY), CURDATE( )

Then use the dates to generate a list element:

" />

(Of course, if your API makes it reasonably easy to perform date calculations, it likely will be more efficient to generate the list of dates on the client side without sending a query to the MySQL server.)