BackUp LevelNext

Dynamic HTML Forms

One of the most important tools for working with relational databases in a Web application is dynamic forms. A dynamic form is an HTML form that uses elements created with database query results and CFML. Most often these elements are either radio buttons, check boxes, select lists, or multiple select lists.

You can use dynamic forms to help ensure data integrity, to speed coding, and to create relationships between tables in your database.

Example: Query form

This example shows a select list of park names that is created with a query against the parks table.

<CFQUERY NAME="ParkNames"
    DATASOURCE="ParkDB">
    SELECT ParkName_ID, ParkName
        FROM Parks
</CFQUERY> 

<FORM ACTION="example.cfm" METHOD="Post">
    <SELECT NAME="ParkName_ID">
    <CFOUTPUT QUERY="ParkNames">
        <OPTION VALUE="#ParkName_ID#">#ParkName#
    </CFOUTPUT>
    </SELECT>
    <INPUT TYPE="submit" VALUE="Submit">
</FORM>

When this form is submitted, it passes the ParkName_ID, which is the primary key for the chosen park. This value can be then used by the example.cfm page. Other elements of a form can be dynamically created in the same way.

The following section explains how to work with checkboxes and multiple select lists that can be created in dynamic forms.

Using checkboxes and multiple select lists in HTML forms

When an HTML form contains either a list of checkboxes with the same name or a multiple select box, the user's entries are made available as a comma-delimited list with the selected values. These lists can be very useful for a wide range of inputs.

Note

If no value is entered for a checkbox or multiple select lists then no variable is created.The SQL INSERT statement will not work correctly if there are no values. To correct this problem, make the form fields required or use Dynamic SQL.

Checkboxes

When you put a series of checkboxes with the same name in an HTML form the variable that is created contains a comma-delimited list of values. This can be done either with numeric values or alphanumeric strings. These two types of values are treated slightly differently.

Searching numeric values

Suppose you want to present a user with a list of organizations. A user is prompted to select one or more organizations using checkboxes. The query retrieves detailed information on the selected organization(s).

Select one or more companies to get information on:

<INPUT TYPE="checkbox"
    NAME="SelectedOrgs" 
    VALUE="5">
    Mobil Corporation<BR>

<INPUT TYPE="checkbox"
    NAME="SelectedOrgs"
    VALUE="19">
    Shapeware, Inc.<BR>

<INPUT TYPE="checkbox"
    NAME="SelectedOrgs"
    VALUE="13">
    BankBoston<BR>

<INPUT TYPE="hidden"
    NAME="SelectedOrgs_required"
    VALUE="You must select at least one organization.">

Note that while the text displayed to the user is the name of the organization, the VALUE attribute of each checkbox corresponds to the underlying database primary key for the organization's record.

If the user checked the Shapeware and BankBoston items, the value of the SelectedOrgs form field would be "19,13." If this parameter were used in the following SQL statement:

SELECT *
    FROM Organizations
    WHERE Organization_ID IN ( #SelectedOrgs# )

the statement sent to the database would be:

SELECT *
    FROM Organizations
    WHERE Organization_ID IN ( 19,13 )

This statement retrieves detailed information on Shapeware and Bank of Boston that you can then display to the user with a CFOUTPUT section.

Searching string values

To search for a database field containing string values (instead of numeric), you must modify both the checkbox and CFQUERY syntax.

In the first example, we searched for company information based on a numeric primary key field called "Organization_ID." If instead the primary key was a database field called "OrganizationName" that contained string values, we must make the following two modifications:

Single quotes must be added to the value attributes of the checkboxes.

<INPUT TYPE="checkbox"
    NAME="SelectedOrgs"
    VALUE="'Mobil Corporation'">
    Mobil Corporation<BR>

<INPUT TYPE="checkbox"
    NAME="SelectedOrgs"
    VALUE="'ShapeWare, Inc.'">
    Shapeware, Inc.<BR>

<INPUT TYPE="checkbox"
    NAME="SelectedOrgs"
    VALUE="'Bank of Boston'">
    BankBoston<BR>

<INPUT TYPE="checkbox"
    NAME="SelectedOrgs_required"
    VALUE="You must select at least one organization.">

If the user checked the Shapeware and BankBoston items, the value of the SelectedOrgs form field would be 'ShapeWare, Inc.','BankBoston'.

You must use the ColdFusion PreserveSingleQuotes function in the SQL statement to prevent ColdFusion from escaping the single quotes in the form field value:

SELECT *
    FROM Organizations
    WHERE OrganizationName IN
    (#PreserveSingleQuotes(SelectedOrgs)#)

The statement sent to the database would be:

SELECT *
    FROM Organizations
    WHERE OrganizationName IN ('ShapeWare, Inc.','BankBoston')

Multiple select lists

ColdFusion treats multiple select lists (HTML input type SELECT with attribute MULTIPLE) just like checkboxes. The data made available to your page from any multiple select list is a comma-delimited list of the entries selected by the user. For example, a multiple select list contains three entries: red, green, and blue. The user selects red and green. The value of the form field variable is then 'red', 'green'.

As with checkboxes, two cases exist when querying for information with multiple select lists: searching a database field containing numeric values, and searching a database field containing string values.

Searching numeric values

For example, suppose you want the user to select organizations from a multiple select box. The query retrieves detailed information on the selected organization(s):

Select one or more companies to get more information on:
<SELECT Name="SelectOrgs" MULTIPLE>
    <OPTION VALUE="5">Mobil Corporation
    <OPTION VALUE="19">ShapeWare, Inc.
    <OPTION VALUE="13">BankBoston
</SELECT>

<INPUT TYPE="hidden"
    NAME="SelectedOrgs_required"
    VALUE="You must select at least one organization.">

If the user selected the Shapeware and BankBoston items, the value of the SelectedOrgs form field would be 19,13.

If this parameter were used in the following SQL statement:

SELECT *
    FROM Organizations
    WHERE Organization_ID IN (#SelectedOrgs#)

the statement sent to the database would be:

SELECT *
    FROM Organizations
    WHERE Organization_ID IN (19,13)

Searching string values

Suppose you want the user to select organizations from a multiple select list. The database field to be searched is a string field. The query retrieves detailed information on the selected organization(s):

Select one or more companies to get 
more information on:
<SELECT Name="SelectOrgs" MULTIPLE>
    <OPTION VALUE="'Mobil Corporation'">Mobil Corporation
    <OPTION VALUE="'ShapeWare, Inc.'">ShapeWare, Inc.
    <OPTION VALUE="'BankBoston'">BankBoston
</SELECT>

<INPUT TYPE="hidden"
    NAME="SelectedOrgs_required"
    VALUE="You must select at least one organization.">

If the user selected the Shapeware and BankBoston items, the value of the SelectedOrgs form field would be 'ShapeWare, Inc.','BankBoston'.

As when using checkboxes to search database fields containing string values, the ColdFusion PreserveSingleQuotes function must be used with multiple select boxes:

SELECT * 
    FROM Organizations
    WHERE OrganizationName IN (#PreserveSingleQuotes(SelectedOrgs)#)

The statement sent to the database would be:

SELECT * 
    FROM Organizations
    WHERE OrganizationName IN ('ShapeWare, Inc.', 'BankBoston')

BackUp LevelNext

allaire

AllaireDoc@allaire.com
Copyright © 1998, Allaire Corporation. All rights reserved.