18.5 Loading a Database Record into a Form
18.5.1 Problem
You want to display a form
but initialize it using the contents of a database record. This
allows you to present a record-editing form.
18.5.2 Solution
Generate the form as you usually would, but instead of using the
usual defaults, set the form elements to the values of columns in the
database record.
18.5.3 Discussion
The form field generation examples shown in earlier sections 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 records 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 = 1\G
*************************** 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. A hidden field is one way 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 scripts for the examples in this section are named
edit_cow.pl, edit_cow.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 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. This is because 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 Recipe 18.3 and Recipe 18.4, 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, "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, "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 timeCreate 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 var="rs" dataSource="${conn}">
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 var="rs" dataSource="${conn}">
SELECT color FROM cow_color ORDER BY color
</sql:query>
<br />Cow color:<br />
<select name="color">
<c:forEach var="row" items="${rs.rows}">
<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 PHP or Python, create the form using the utility functions
developed in Recipe 18.3 and Recipe 18.4. See the
cow_edit.php and
cow_edit.py scripts for details.
|
Main Menu
|
