Recipe 19.2. Creating Single-Pick Form Elements from Database Content

Problem

A form needs to present a field that offers several options but enables the user to select only one of them.

Solution

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

Discussion

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

The list of colors in the cow_color table. These can be obtained with the following statement:
```
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. (We'll perform encoding for all the list elements in the form as a matter of course, but those 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 from INFORMATION_SCHEMA:

mysql> SELECT COLUMN_TYPE, COLUMN_DEFAULT     -> FROM INFORMATION_SCHEMA.COLUMNS     -> WHERE TABLE_SCHEMA='cookbook' AND TABLE_NAME='cow_order'     -> AND COLUMN_NAME='size'; +--------------------------------+----------------+ | COLUMN_TYPE                    | COLUMN_DEFAULT | +--------------------------------+----------------+ | enum('small','medium','large') | medium         | +--------------------------------+----------------+

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 the lists just described. As shown, there are 3 figurine sizes, 7 colors, and 50 states. The differing numbers of choices lead to different decisions about how to represent the lists in a form:

The figurine 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 enable 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.

The following discussion describes the HTML syntax for these types of elements and then shows how to generate them from within scripts:

Radio buttons

A group of radio buttons consists of <input> 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 <input> 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:

<input type="radio" name="size" value="small" />small <input type="radio" name="size" value="medium" checked="checked" />medium <input type="radio" name="size" value="large" />large

Pop-up menus

A pop-up menu is a list that begins and ends with <select> and </select> tags, with each item in the menu enclosed within <option> and </option> tags. Each <option> 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 <option> item. If no item is so marked, the first item becomes the default, as is the case for the following pop-up menu:

<select name="color"> <option value="Black">Black</option> <option value="Black &amp; White">Black &amp; White</option> <option value="Brown">Brown</option> <option value="Cream">Cream</option> <option value="Red">Red</option> <option value="Red &amp; White">Red &amp; White</option> <option value="See-Through">See-Through</option> </select>

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 <select> 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; Section 19.3 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:

<select name="state" size="6"> <option value="AL">Alabama</option> <option value="AK">Alaska</option> <option value="AZ">Arizona</option> <option value="AR">Arkansas</option> <option value="CA">California</option> ... <option value="WV">West Virginia</option> <option value="WI">Wisconsin</option> <option value="WY">Wyoming</option> </select>

Radio button sets, pop-up menus, and scrolling lists 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 which values are available to be selected.
A set of labels, one for each item: These determine what the user sees when the form is displayed.
An optional default value: This determines which item in the list is selected initially when the browser displays the list.

To produce a list element for a form using database content, issue a statement that selects 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 desire 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 these are produced by fetching a set of column values from a table. Then we'll construct the figurine size list, which takes its values from a column's definition rather than its contents.

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:

<sql:query dataSource="${conn}" var="rs">   SELECT color FROM cow_color ORDER BY color </sql:query> <c:forEach items="${rs.rows}" var="row">   <input type="radio" name="color"     value="<c:out value="${row.color}"/>"   /><c:out value="${row.color}"/><br /> </c:forEach>

<c:out> performs HTML entity encoding, so the & character that is present in some of the color values is converted to & automatically and does not cause display problems in the resulting web page.

To display a pop-up menu instead, the retrieval statement is the same, but the row-fetching loop is different:

<sql:query dataSource="${conn}" var="rs">   SELECT color FROM cow_color ORDER BY color </sql:query> <select name="color"> <c:forEach items="${rs.rows}" var="row">   <option value="<c:out value="${row.color}"/>">   <c:out value="${row.color}"/></option> </c:forEach> </select>

The pop-up menu can be changed easily to a scrolling list. All you need to do is add a size attribute to the opening <select> tag. For example, to make three colors visible at a time, generate the list like this:

<sql:query dataSource="${conn}" var="rs">   SELECT color FROM cow_color ORDER BY color </sql:query> <select name="color" size="3"> <c:forEach items="${rs.rows}" var="row">   <option value="<c:out value="${row.color}"/>">   <c:out value="${row.color}"/></option> </c:forEach> </select>

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, and then insert them into the proper parts of each list item. For example, to create a pop-up menu, do this:

<sql:query dataSource="${conn}" var="rs">   SELECT abbrev, name FROM states ORDER BY name </sql:query> <select name="state"> <c:forEach items="${rs.rows}" var="row">   <option value="<c:out value="${row.abbrev}"/>">   <c:out value="${row.name}"/></option> </c:forEach> </select>

The preceding 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, and 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: What you want to call the element.
values: The values for the items in the list. This should be a reference to an array.
labels: 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 should be a reference to a hash that maps each state abbreviation to its full name.
default: 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.)

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 we're not going to be that fancy.)

To construct a form element using the colors in the cow_color table, begin by retrieving them into an array:

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

selectcol_arrayref⁠(⁠ ⁠ ⁠) returns a reference to the array, which is the kind of value needed for the values argument of the CGI.pm functions that create list elements. 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 an & character. CGI.pm functions for generating form elements automatically perform HTML-encoding, unlike its functions for creating nonform 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

The Ruby cgi module also has methods for generating radio buttons, pop-up menus, and scrolling lists. You can examine the form_element.rb script to see how to use them. However, I am not going to discuss them here because I find them awkward to use, particularly when it's necessary to ensure that values are properly escaped or that certain group members are selected by default.

If you're using an API that doesn't provide a ready-made set of functions for producing form elements (or those functions are inconvenient to use), 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 menu as follows using a fetch-and-print loop like this:

print ("<select name=\"color\">\n"); $stmt = "SELECT color FROM cow_color ORDER BY color"; $result =& $conn->query ($stmt); if (!PEAR::isError ($result)) {   while (list ($color) = $result->fetchRow ())   {     $color = htmlspecialchars ($color);     print ("<option value=\"$color\">$color</option>\n");   }   $result->free (); } print ("</select>\n");

Python code to do the same is similar:

stmt = "SELECT color FROM cow_color ORDER BY color" cursor = conn.cursor () cursor.execute (stmt) print "<select name=\"color\">" for (color, ) in cursor.fetchall ():   color = cgi.escape (color, 1)   print "<option value=\"%s\">%s</option>" % (color, color) cursor.close () print "</select>"

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

print ("<select name=\"state\">\n"); $stmt = "SELECT abbrev, name FROM states ORDER BY name"; $result =& $conn->query ($stmt); if (!PEAR::isError ($result)) {   while ($row =& $result->fetchRow ())   {     $abbrev = htmlspecialchars ($row[0]);     $name = htmlspecialchars ($row[1]);     print ("<option value=\"$abbrev\">$name</option>\n");   }   $result->free (); } print ("</select>\n");

And in Python, like this:

stmt = "SELECT abbrev, name FROM states ORDER BY name" cursor = conn.cursor () cursor.execute (stmt) print "<select name=\"state\">" for (abbrev, name) in cursor.fetchall ():   abbrev = cgi.escape (abbrev, 1)   name = cgi.escape (name, 1)   print "<option value=\"%s\">%s</option>" % (abbrev, name) cursor.close () print "</select>"

Radio buttons and scrolling lists can be produced in similar fashion. But rather than doing so, let's use 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. They're invoked as follows:

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: 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: 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 Section 19.3), 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: This applies to radio button groups. If true, it indicates that the items should be stacked vertically rather than horizontally.
size, multiple: These 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 of argument values that are incorporated into the list. (The Ruby version of the library file includes utility methods for generating these elements, too, even though the cgi module has methods for creating them; I think the utility methods are easier to use than the cgi methods.)

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

function make_radio_group ($name, $values, $labels, $default, $vertical) {   $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 (                 "<input type=\"radio\" name=\"%s\" value=\"%s\"%s />%s",                 htmlspecialchars ($name),                 htmlspecialchars ($values[$i]),                 $checked,                 htmlspecialchars ($labels[$i]));     if ($vertical)       $str .= "<br />"; # display items vertically     $str .= "\n";   }   return ($str); }

The function constructs the form element as a string, which it returns. To use make_radio_group⁠(⁠ ⁠ ⁠) to present cow colors, invoke it after fetching the items from the cow_color table, as follows:

$values = array (); $stmt = "SELECT color FROM cow_color ORDER BY color"; $result =& $conn->query ($stmt); if (!PEAR::isError ($result)) {   while ($row =& $result->fetchRow ())     $values[] = $row[0];   $result->free (); } print (make_radio_group ("color", $values, $values, "", TRUE));

The $values array is passed to make_radio_group⁠(⁠ ⁠ ⁠) twice because it's used both for 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) {   $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 (               "<option value=\"%s\"%s>%s</option>\n",               htmlspecialchars ($values[$i]),               $checked,               htmlspecialchars ($labels[$i]));   }   $str = sprintf (             "<select name=\"%s\">\n%s</select>\n",             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 labels that are different from the values. Fetch the labels and values like this:

$values = array (); $labels = array (); $stmt = "SELECT abbrev, name FROM states ORDER BY name"; $result =& $conn->query ($stmt); if (!PEAR::isError ($result)) {   while ($row =& $result->fetchRow ())   {     $values[] = $row[0];     $labels[] = $row[1];   }   $result->free (); }

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

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

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

def make_popup_menu (name, values, labels, default):   result_str = ""   # make sure name and default are strings   name = str (name)   default = str (default)   for i in range (len (values)):     # make sure value and label are strings     value = str (values[i])     label = str (labels[i])     # select the item if it corresponds to the default value     if value == default:       checked = " selected=\"selected\""     else:       checked = ""     result_str = result_str + \           "<option value=\"%s\"%s>%s</option>\n" \             % (cgi.escape (value, 1),               checked,               cgi.escape (label, 1))   result_str = "<select name=\"%s\">\n%s</select>\n" \         % (cgi.escape (name, 1), result_str)   return result_str

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

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

Then convert the list to a form element using one of the following calls:

print make_radio_group ("color", values, values, "", True) print make_popup_menu ("color", values, values, "") print make_scrolling_list ("color", values, values, "", 3, False)

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

values = [] labels = [] stmt = "SELECT abbrev, name FROM states ORDER BY name" cursor = conn.cursor () cursor.execute (stmt) 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, False)

Something the Ruby and Python utility methods in the lib directory do that their PHP counterparts do not is explicitly convert to string form all argument values that get incorporated into the list. This is necessary because the Ruby CGI.escapeHTML⁠(⁠ ⁠ ⁠) and Python cgi.escape⁠(⁠ ⁠ ⁠) methods raise an exception if you try to use them to HTML-encode nonstring values.

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 definition of 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. In other words, we need to use the column metadata rather than the column data.

As it happens, a lot of the work involved in this task has already been done in Section 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, "cookbook", "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 Ruby metadata-fetching method returns a hash. Use it as follows to generate form elements from the size column metadata:

size_info = get_enumorset_info(dbh, "cookbook", "cow_order", "size") form << make_radio_group("size",                          size_info["values"],                          size_info["values"],                          size_info["default"],                          true)    # display items vertically form << make_popup_menu("size",                         size_info["values"],                         size_info["values"],                         size_info["default"])

The metadata function for PHP returns an associative array, which is used in similar fashion:

$size_info = get_enumorset_info ($conn, "cookbook", "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 metadata function returns a dictionary:

size_info = get_enumorset_info (conn, "cookbook", "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"])

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 JSTL tags to query INFORMATION_SCHEMA for the ENUM column definition, and then move the definition into page context.
Invoke 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:

<%@ page import="java.util.*" %> <%@ page import="java.util.regex.*" %> <%! // 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) {   String typeDef = ctx.getAttribute (typeDefAttr).toString ();   List values = new ArrayList ();   // column must be an ENUM or SET   Pattern pc = Pattern.compile ("(enum|set)\\((.*)\\)",                                 Pattern.CASE_INSENSITIVE);   Matcher m = pc.matcher (typeDef);   // matches() fails unless it matches entire string   if (m.matches ())   {     // split value list on commas, trim quotes from end of each word     String[] v = m.group (2).split (",");     for (int i = 0; i < v.length; i++)       values.add (v[i].substring (1, v[i].length() - 1));   }   ctx.setAttribute (valListAttr, values); } %>

The function takes three arguments:

ctx: The page context object.
typeDefAttr: The name of the page attribute that contains the column definition. This is the function "input."
valListAttr: 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:

<sql:query dataSource="${conn}" var="rs">   SELECT COLUMN_TYPE, COLUMN_DEFAULT   FROM INFORMATION_SCHEMA.COLUMNS   WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'cow_order'   AND COLUMN_NAME = 'size' </sql:query> <c:set var="typeDef" scope="page" value="${rs.rowsByIndex[0][0]}"/> <% getEnumOrSetValues (pageContext, "typeDef", "values"); %> <c:set var="default" scope="page" value="${rs.rowsByIndex[0][1]}"/>

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

<c:forEach items="${values}" var="val">   <input type="radio" name="size"     value="<c:out value="${val}"/>"     <c:if test="${val == default}">checked="checked"</c:if>   /><c:out value="${val}"/><br /> </c:forEach>

or a pop-up menu like this:

<select name="size"> <c:forEach items="${values}" var="val">   <option     value="<c:out value="${val}"/>"     <c:if test="${val == default}">selected="selected"</c:if>   >   <c:out value="${val}"/></option> </c:forEach> </select>

Don't Forget to HTML-Encode All List Content in Forms

The Ruby, PHP, and Python utility routines described in this recipe for generating list elements perform HTML-encoding of attribute values for the HTML tags that make up the list, such as the name and value attributes. They also encode the labels. I've noticed that many published accounts of list generation do not do this, or they encode the labels but not the values. That is a mistake. If either the label or the value contains a special character like & or <, the browser may misinterpret them, and your application will misbehave. It's also important to make sure that your encoding function turns double quotes into " entities (or ", which is equivalent), because tag attributes are so often enclosed within double quotes. Failing to convert a double quote to the entity name in an attribute value results in a double quote within a double-quoted string, which is malformed.

If you're using the Perl CGI.pm module or the JSTL tags to produce HTML for form elements, encoding is taken care of for you. CGI.pm's form-related functions automatically perform encoding. Similarly, using the JSTL <c:out> tag to write attribute values from JSP pages will produce properly encoded values.

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 enable a user to pick a table name in a database administration application, you can generate a scrolling list that contains an item for each table in the database. A CGI.pm-based script might do so like this:

my $stmt = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES             WHERE TABLE_SCHEMA = 'cookbook' ORDER BY TABLE_NAME"; my $table_ref = $dbh->selectcol_arrayref ($stmt); 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 statement:

<sql:query dataSource="${conn}" var="rs">   SELECT     CURDATE() - INTERVAL 6 DAY,     CURDATE() - INTERVAL 5 DAY,     CURDATE() - INTERVAL 4 DAY,     CURDATE() - INTERVAL 3 DAY,     CURDATE() - INTERVAL 2 DAY,     CURDATE() - INTERVAL 1 DAY,     CURDATE() </sql:query>

Then use the dates to generate a list element:

<c:set var="dateList" value="${rs.rowsByIndex[0]}"/> <c:forEach items="${dateList}" var="date">   <input type="radio" name="date"     value="<c:out value="${date}"/>"   /><c:out value="${date}"/><br /> </c:forEach>

Of course, if your programming language makes it reasonably easy to perform date calculations, it is more efficient to generate the list of dates on the client side without sending a statement to the MySQL server.