Problem 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. Solution 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. Discussion 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 = 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. 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 Section 19.2 and Section 19.3, 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 Section 19.2 and Section 19.3. See the cow_edit.rb, cow_edit.php, and cow_edit.py scripts for details. |
