A form needs to present a field that offers several options but enables the user to select only one of them.
Use a single-pick list element. These include radio button sets, pop-up menus, and scrolling lists.
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 scrolling—particularly 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:
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
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 & White">Black & White</option> <option value="Brown">Brown</option> <option value="Cream">Cream</option> <option value="Red">Red</option> <option value="Red & White">Red & White</option> <option value="See-Through">See-Through</option> </select>
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;
Creating Multiple-Pick Form Elements from Database
Content 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:
When the user submits the form, the browser associates this name with whatever value the user selected.
These determine which values are available to be selected.
These determine what the user sees when the form is displayed.
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
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"> "); $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> "); } $result->free (); } print ("</select> ");
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"> "); $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> "); } $result->free (); } print ("</select> ");
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 Creating Multiple-Pick Form Elements from Database
Content), 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 .= " "; } 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> ", htmlspecialchars ($values[$i]), $checked, htmlspecialchars ($labels[$i])); } $str = sprintf ( "<select name="%s"> %s</select> ", 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> " % (cgi.escape (value, 1), checked, cgi.escape (label, 1)) result_str = "<select name="%s"> %s</select> " % (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 Getting ENUM and SET Column Information, 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>
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.
18.191.215.96