Recipe 19.3. Creating Multiple-Pick Form Elements from Database Content

Problem

A form needs to present a field that offers several options and enables the user to select any number of them.

Solution

Use a multiple-pick list element, such as a set of checkboxes or a scrolling list.

Discussion

Multiple-pick form elements enable you to present multiple choices, any number of which can be selected, or possibly even none of them. For our example scenario in which customers order cow figurines online, the multiple-pick element is represented by the set of accessory items that are available. The accessory column in the cow_order table is represented as a SET, so the allowable and default values can be obtained with the following statement:

mysql> SELECT COLUMN_TYPE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS     -> WHERE TABLE_SCHEMA='cookbook' AND TABLE_NAME='cow_order'     -> AND COLUMN_NAME='accessories'; +---------------------------------------------------+----------------+ | COLUMN_TYPE                                       | COLUMN_DEFAULT | +---------------------------------------------------+----------------+ | set('cow bell','horns','nose ring','tail ribbon') | cow bell,horns | +---------------------------------------------------+----------------+ 

The values listed in the definition can reasonably be represented as either a set of checkboxes or as a multiple-pick scrolling list. Either way, the cow bell and horns items should be selected initially, because each is present in the column's default value. The following discussion shows the HTML syntax for these elements, and then describes how to generate them from within scripts.

NOTE

The material in this section relies heavily on Section 19.2, which discusses radio buttons, pop-up menus, and single-pick scrolling lists. I assume that you've already read that section.

Checkboxes

A group of checkboxes is similar to a group of radio buttons in that it consists of <input> elements that all have the same name attribute. However, the type attribute is checkbox rather than radio, and you can specify checked for as many items in the group as you want to be selected by default. If no items are marked as checked, none are selected initially. The following checkbox set shows the cow accessory items with the first two items selected by default:

<input type="checkbox" name="accessories" value="cow bell"   checked="checked" />cow bell <input type="checkbox" name="accessories" value="horns"   checked="checked" />horns <input type="checkbox" name="accessories" value="nose ring" />nose ring <input type="checkbox" name="accessories" value="tail ribbon" />tail ribbon 

Scrolling lists

A multiple-pick scrolling list has most syntax in common with its single-pick counterpart. The differences are that you include a multiple attribute in the opening <select> tag, and default value selection is different. For a single-pick list, you can add selected to at most one item, and the first item is selected by default in the absence of an explicit selected attribute. For a multiple-pick list, you can add a selected attribute to as many of the items as you like, and no items are selected by default in the absence of selected attributes.

If the set of cow accessories is represented as a multiple-pick scrolling list with cow bell and horns selected initially, it looks like this:

<select name="accessories" size="3" multiple="multiple"> <option value="cow bell" selected="selected">cow bell</option> <option value="horns" selected="selected">horns</option> <option value="nose ring">nose ring</option> <option value="tail ribbon">tail ribbon</option> </select> 

In CGI.pm-based Perl scripts, you create checkbox sets or scrolling lists by invoking checkbox_group⁠(⁠ ⁠ ⁠) or scrolling_list⁠(⁠ ⁠ ⁠). These functions take name, values, labels, and default arguments, just like their single-pick cousins. But because multiple items can be selected initially, CGI.pm allows the default argument to be specified as either a scalar value or a reference to an array of values. (It also accepts the argument name defaults as a synonym for default.)

To get the list of legal values for a SET column, we can do the same thing as in Section 19.2 for ENUM columnsthat is, call a utility routine that returns the column metadata:

my $acc_info = get_enumorset_info ($dbh, "cookbook", "cow_order", "accessories"); 

However, the default value for a SET column is not in a form that is directly usable for form element generation. MySQL represents SET default values as a list of zero or more items, separated by commas; for example, the default for the accessories column is cow bell,horns. That doesn't match the list-of-values format that the CGI.pm functions expect, so it's necessary to split the default value at the commas to obtain an array. The following expression shows how to do so, taking into account the possibility that the default column value might be undef (NULL):

my @acc_def = (defined ($acc_info->{default})                 ? split (/,/, $acc_info->{default})                 : () ); 

After splitting the default value, pass the resulting array by reference to whichever of the list-generating functions you want to use:

print checkbox_group (-name => "accessories",                       -values => $acc_info->{values},                       -default => \@acc_def,                       -linebreak => 1);   # display buttons vertically print scrolling_list (-name => "accessories",                       -values => $acc_info->{values},                       -default => \@acc_def,                       -size => 3,         # display 3 items at a time                       -multiple => 1);    # create multiple-pick list 

When you use SET values like this to create list elements, the values are displayed in the order they are listed in the column definition. That may not correspond to the order in which you want them to appear; if not, sort the values appropriately.

For Ruby, PHP, and Python, we can create utility functions to generate multiple-pick items. They'll have the following invocation syntax:

make_checkbox_group (name, values, labels, default, vertical) make_scrolling_list (name, values, labels, default, size, multiple) 

The name, values, and labels arguments to these functions are similar to those of the single-pick utility routines described in Section 19.2. make_checkbox_group⁠(⁠ ⁠ ⁠) takes a vertical argument to indicate whether the items should be stacked vertically rather than horizontally. make_scrolling_list⁠(⁠ ⁠ ⁠) has already been described in Section 19.2 for producing single-pick lists. To use it here, the multiple argument should be true to produce a multiple-pick list. For both functions, the default argument can be an array of multiple values if several items should be selected initially.

make_checkbox_group⁠(⁠ ⁠ ⁠) looks like this (shown here in Ruby; the PHP and Python versions are similar):

def make_checkbox_group(name, values, labels, default, vertical)   # make sure default is an array (converts a scalar to an array)   default = [ default ].flatten   str = ""   for i in 0...values.length do     # select the item if it corresponds to one of the default values     checked = (default.include?(values[i]) ? " checked=\"checked\"" : "")     str << sprintf(                 "<input type=\"checkbox\" name=\"%s\" value=\"%s\"%s />%s",                 CGI.escapeHTML(name.to_s),                 CGI.escapeHTML(values[i].to_s),                 checked,                 CGI.escapeHTML(labels[i].to_s))     str << "<br />" if vertical   # display items vertically     str << "\n"   end   return str end 

To fetch the cow accessory information and present it using checkboxes, do this:

acc_info = get_enumorset_info(dbh, "cookbook", "cow_order", "accessories") if acc_info["default"].nil?   acc_def = [] else   acc_def = acc_info["default"].split(",") end form << make_checkbox_group("accessories",                             acc_info["values"],                             acc_info["values"],                             acc_def,                             true)      # display items vertically 

To display a scrolling list instead, invoke make_scrolling_list⁠(⁠ ⁠ ⁠):

form << make_scrolling_list("accessories",                             acc_info["values"],                             acc_info["values"],                             acc_def,                             3,         # display 3 items at a time                             true)      # create multiple-pick list 

In PHP, fetch the accessory information, and then present checkboxes or a scrolling list as follows:

$acc_info = get_enumorset_info ($conn, "cookbook", "cow_order", "accessories"); $acc_def = explode (",", $acc_info["default"]); print (make_checkbox_group ("accessories[]",                             $acc_info["values"],                             $acc_info["values"],                             $acc_def,                             TRUE));   # display items vertically print (make_scrolling_list ("accessories[]",                             $acc_info["values"],                             $acc_info["values"],                             $acc_def,                             3,        # display 3 items at a time                             TRUE));   # create multiple-pick list 

Note that the field name in the PHP examples is specified as accessories[] rather than as accessories. In PHP, you must add [] to the name if you want to allow a field to have multiple values. If you omit the [], the user will be able to select multiple items while filling in the form, but PHP will return only one of them to your script. This issue comes up again when we discuss how to process the contents of submitted forms in Section 19.5.

In Python, to fetch the cow accessory information and present it using checkboxes or a scrolling list, do this:

acc_info = get_enumorset_info (conn, "cookbook", "cow_order", "accessories") if acc_info["default"] == None:   acc_def = "" else:   acc_def = acc_info["default"].split (",") print make_checkbox_group ("accessories",                            acc_info["values"],                            acc_info["values"],                            acc_def,                            True)   # display items vertically print make_scrolling_list ("accessories",                            acc_info["values"],                            acc_info["values"],                            acc_def,                            3,      # display 3 items at a time                            True)   # create multiple-pick list 

In JSP pages, the getEnumOrSetValues⁠(⁠ ⁠ ⁠) function used earlier to get the value list for the size column (an ENUM) can also be used for the accessory column (a SET). The column definition and default value can be obtained from INFORMATION_SCHEMA. Query the COLUMNS table, parse the type definition into a list of values named values, and put the default value in defList like this:

<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 = 'accessories' </sql:query> <c:set var="typeDef" scope="page" value="${rs.rowsByIndex[0][0]}"/> <% getEnumOrSetValues (pageContext, "typeDef", "values"); %> <c:set var="defList" scope="page" value="${rs.rowsByIndex[0][1]}"/> 

For a SET column, the defList value might contain multiple values, separated by commas. It needs no special treatment; the JSTL <c:forEach> tag can iterate over such a string, so initialize the default values for a checkbox set as follows:

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

For a multiple-pick scrolling list, do this:

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