You want to display a form but initialize it using the contents of a database record. This enables you to present a record-editing form.
Generate the form as you usually would, but populate it with database content. That is, instead of setting the form element defaults to their usual values, set them to the values of columns in the database record.
The examples in earlier recipes that show how to generate form
fields have either supplied no default value or have used the default
value as specified in an ENUM
or
SET
column definition as the field
default. That’s most appropriate for presenting a “blank”
form that you expect the user to fill in. However, for applications
that present a web-based interface for record editing, it’s more
likely that you’d want to fill in the form using the content of an
existing record for the initial values. This section discusses how to
do that.
The examples shown here illustrate how to generate an editing
form for rows from the cow_order
table. Normally, you would allow the user to specify which record to
edit. For simplicity, assume the use of the record that has an
id
value of 1
, with the following contents:
mysql>SELECT * FROM cow_order WHERE id = 1G
*************************** 1. row ***************************
id: 1
color: Black & White
size: large
accessories: cow bell,nose ring
cust_name: Farmer Brown
cust_street: 123 Elm St.
cust_city: Katy
cust_state: TX
To generate a form with contents that correspond to a database record, use the column values for the element defaults as follows:
For <input>
elements such as radio buttons or checkboxes, add a checked
attribute to each list item that
matches the column value.
For <select>
elements such as pop-up menus or scrolling lists, add a
selected
attribute to each list
item that matches the column value.
For text fields represented as <input>
elements of type text
, set the value
attribute to the corresponding
column value. For example, a 60-character field for cust_name
can be presented initialized
to Farmer
Brown
like this:
<input type="text" name="cust_name" value="Farmer Brown" size="60" />
To present a <textarea>
element instead, set the body to the column value. To
create a field 40 columns wide and 3 rows high, write it like
this:
<textarea name="cust_name" cols="40" rows="3"> Farmer Brown </textarea>
In a record-editing situation, it’s a good idea to include a
unique value in the form so that you can tell which record the
form contents represent when the user submits it. Use a hidden
field to do this. Its value is not displayed to the user, but the
browser returns it with the rest of the field values. Our sample
record has an id
column with a
value of 1
, so the hidden field
looks like this:
<input type="hidden" name="id" value="1" />
The following examples show how to produce a form with id
represented as a hidden field, color
as a pop-up menu, size
as a set of radio buttons, and accessories
as a set of checkboxes. The
customer information values are represented as text input boxes,
except that cust_state
is a
single-pick scrolling list. You could make other choices, of course,
such as to present the sizes as a pop-up menu rather than as radio
buttons.
The recipes
distribution
scripts for the examples in this section are named cow_edit.pl, cow_edit.jsp, and so forth.
The following procedure outlines how to load the sample cow_table
record into an editing form for a
CGI.pm-based Perl script:
Retrieve the column values for the record that you want to load into the form:
my $id = 1; # select record number 1 my ($color, $size, $accessories, $cust_name, $cust_street, $cust_city, $cust_state) = $dbh->selectrow_array ( "SELECT color, size, accessories, cust_name, cust_street, cust_city, cust_state FROM cow_order WHERE id = ?", undef, $id);
Begin the form:
print start_form (-action => url ());
Generate the hidden field containing the id
value that uniquely identifies the
cow_order
record:
print hidden (-name => "id", -value => $id, -override => 1);
The override
argument
forces CGI.pm to use the value specified in the value
argument as the hidden field
value. If override
is not true,
CGI.pm normally tries to use values present in the script
execution environment to initialize form fields, even if you
provide values in the field-generating calls. (CGI.pm does this to
make it easier to redisplay a form with the values the user just
submitted. For example, if you find that a form has been filled in
incorrectly, you can redisplay it and ask the user to correct any
problems. To make sure that a form element contains the value you
specify, it’s necessary to override this behavior.)
Create the fields that describe the cow figurine
specifications. These fields are generated the same way as
described in Recipes and , except that the default values
come from the contents of record 1. The code here presents
color
as a pop-up menu,
size
as a set of radio buttons,
and accessories
as a set of
checkboxes. Note that it splits the accessories
value at commas to produce
an array of values, because the column value might name several
accessory items:
my $color_ref = $dbh->selectcol_arrayref ( "SELECT color FROM cow_color ORDER BY color"); print br (), "Cow color:", br (); print popup_menu (-name => "color", -values => $color_ref, -default => $color, -override => 1); my $size_info = get_enumorset_info ($dbh, "cookbook", "cow_order", "size"); print br (), "Cow figurine size:", br (); print radio_group (-name => "size", -values => $size_info->{values}, -default => $size, -override => 1, -linebreak => 1); my $acc_info = get_enumorset_info ($dbh, "cookbook", "cow_order", "accessories"); my @acc_val = (defined ($accessories) ? split (/,/, $accessories) : () ); print br (), "Cow accessory items:", br (); print checkbox_group (-name => "accessories", -values => $acc_info->{values}, -default => @acc_val, -override => 1, -linebreak => 1);
Create the customer information fields. These are represented as text input fields, except the state, which is shown here as a single-pick scrolling list:
print br (), "Customer name:", br (); print textfield (-name => "cust_name", -value => $cust_name, -override => 1, -size => 60); print br (), "Customer street address:", br (); print textfield (-name => "cust_street", -value => $cust_street, -override => 1, -size => 60); print br (), "Customer city:", br (); print textfield (-name => "cust_city", -value => $cust_city, -override => 1, -size => 60); 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 } print br (), "Customer state:", br (); print scrolling_list (-name => "cust_state", -values => @state_values, -labels => \%state_labels, -default => $cust_state, -override => 1, -size => 6); # display 6 items at a time
Create a form submission button and terminate the form:
print br (), submit (-name => "choice", -value => "Submit Form"), end_form ();
The same general procedure applies to other APIs. For example, in a JSP page, you can fetch the record to be edited and extract its contents into scalar variables like this:
<c:set var="id" value="1"/> <sql:query dataSource="${conn}" var="rs"> SELECT id, color, size, accessories, cust_name, cust_street, cust_city, cust_state FROM cow_order WHERE id = ? <sql:param value="${id}"/> </sql:query> <c:set var="row" value="${rs.rows[0]}"/> <c:set var="id" value="${row.id}"/> <c:set var="color" value="${row.color}"/> <c:set var="size" value="${row.size}"/> <c:set var="accessories" value="${row.accessories}"/> <c:set var="cust_name" value="${row.cust_name}"/> <c:set var="cust_street" value="${row.cust_street}"/> <c:set var="cust_city" value="${row.cust_city}"/> <c:set var="cust_state" value="${row.cust_state}"/>
Then use the values to initialize the various form elements, such as:
The hidden field for the ID value:
<input type="hidden" name="id" value="<c:out value="${id}"/>"/>
The color
pop-up
menu:
<sql:query dataSource="${conn}" var="rs"> SELECT color FROM cow_color ORDER BY color </sql:query> <br />Cow color:<br /> <select name="color"> <c:forEach items="${rs.rows}" var="row"> <option value="<c:out value="${row.color}"/>" <c:if test="${row.color == color}">selected="selected"</c:if> ><c:out value="${row.color}"/></option> </c:forEach> </select>
The cust_name
text
field:
<br />Customer name:<br /> <input type="text" name="cust_name" value="<c:out value="${cust_name}"/>" size="60" />
For Ruby, PHP, or Python, create the form using the utility functions developed in Recipes and . See the cow_edit.rb, cow_edit.php, and cow_edit.py scripts for details.
3.138.135.80