ProblemA form needs to present a field that offers several options and enables the user to select any number of them. SolutionUse a multiple-pick list element, such as a set of checkboxes or a scrolling list. DiscussionMultiple-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.
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> |