MySQL Cookbook

18.4 Creating Multiple-Pick Form Elements from Database Content

18.4.1 Problem

A form needs to present a field that offers the user a set of options and allows any number of them to be selected.

18.4.2 Solution

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

18.4.3 Discussion

Multiple-pick form elements allow 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 from the following query:

mysql> SHOW COLUMNS FROM cow_order LIKE 'accessories'\G
*************************** 1. row ***************************
  Field: accessories
   Type: set('cow bell','horns','nose ring','tail ribbon')
   Null: YES
    Key:
Default: cow bell,horns
  Extra:

This set of items can reasonably be represented as either a set of checkboxes or as a multiple-pick scrolling list. In both cases, the cow bell and horns items should be selected initially, because each is present in the column's default value. I will discuss the HTML syntax for these elements, then show how to generate them from within scripts. (The material in this section relies heavily on Recipe 18.3, which discusses radio buttons, pop-up menus, and single-pick scrolling lists. I assume 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

• CScrolling list

  A multiple-pick scrolling list is constructed in much the same manner as its single-pick counterpart. The differences are that you include a multiple attribute in the opening <select> tag, and the default value behavior 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. With 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 Recipe 18.3 for ENUM columns—that is, call a utility routine that returns the column metadata:

my $acc_info = get_enumorset_info ($dbh, "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 comma-separated list of items. (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 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 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 PHP and Python single-pick utility routines described in Recipe 18.3. make_checkbox_group( ) takes a vertical argument that indicates that the items should be stacked vertically rather than horizontally if it's true. make_scrolling_list( ) has already been described in Recipe 18.3 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 Python; the PHP version is similar):

def make_checkbox_group (name, values, labels, default, vertical):
    if type (values) not in (types.ListType, types.TupleType):
        return ("make_checkbox_group: values argument must be a list")
    if type (labels) not in (types.ListType, types.TupleType):
        return ("make_checkbox_group: labels argument must be a list")
    if len (values) != len (labels):
        return ("make_checkbox_group: value and label list size mismatch")
    if type (default) not in (types.ListType, types.TupleType):
        default = [ default ]       # convert scalar to list
    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 one of the default values
        checked = ""
        for d in default:
            if type (d) is not types.StringType:
                d = `d`
            if value == d:
                checked = " checked=\"checked\""
                break
        if type (name) is not types.StringType:
            name = `name`
        str = str + \
                "<input type=\"checkbox\" name=\"%s\" value=\"%s\"%s />%s" \
                    % (cgi.escape (name, 1),
                        cgi.escape (value, 1),
                        checked,
                        cgi.escape (label, 1))
        if vertical:
            str = str + "<br />"    # display items vertically
        str = str + "\n"
    return (str)

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

import re            # needed for re.split( )

acc_info = get_enumorset_info (conn, "cow_order", "accessories")
if acc_info["default"] == None:
    acc_def = ""
else:
    acc_def = re.split (",", acc_info["default"])

print make_checkbox_group ("accessories",
                            acc_info["values"],
                            acc_info["values"],
                            acc_def,
                            1)          # display items vertically

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

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

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

$acc_info = get_enumorset_info ($conn_id, "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 will come up again when we discuss how to process the contents of submitted forms in Recipe 18.6.

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 are in the second and fifth column of the SHOW COLUMNS query that returns information about the accessory column. Run the query, parse the type definition into a list of values named values, and put the default value in defList like this:

<sql:query var="rs" dataSource="${conn}">
    SHOW COLUMNS FROM cow_order LIKE 'accessories'
</sql:query>
<c:set var="typeDef" scope="page" value="${rs.rowsByIndex[0][1]}" />
<% getEnumOrSetValues (pageContext, "typeDef", "values"); %>
<c:set var="defList" scope="page" value="${rs.rowsByIndex[0][4]}" />

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

<c:forEach var="val" items="${values}">
    <input type="checkbox" name="accessories"
        value="<c:out value="${val}" />"
        <c:forEach var="default" items="${defList}">
            <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 var="val" items="${values}">
    <option
        value="<c:out value="${val}" />"
        <c:forEach var="default" items="${defList}">
            <c:if test="${val == default}">selected="selected"</c:if>
        </c:forEach>
    >
    <c:out value="${val}" /></option>
</c:forEach>
</select>