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 = 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 elements such as radio buttons or checkboxes, add a checked attribute to each list item that matches the column value.
• For 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 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:

   

  To present a </TT> element instead, set the body to the column value. To create a field 40 columns wide and 3 rows high, write it like this: </P> <PRE><textarea name="cust_name" cols="40" rows="3"> Farmer Brown

• 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:

   

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:

1. 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);

2. Begin the form:

   print start_form (-action => url ( ));

3. 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.)

4. 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);

5. 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

6. 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:

SELECT id, color, size, accessories, cust_name, cust_street, cust_city, cust_state FROM cow_order WHERE id = ?

Then use the values to initialize the various form elements, such as:

• The hidden field for the ID value:

  " />

• The color pop-up menu:

  SELECT color FROM cow_color ORDER BY color Cow color: " selected="selected" >

• The cust_name text field:

  Customer 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.