Developing ColdFusion MX Applications with CFML
Retrieving and Formatting Data

Creating dynamic check boxes and multiple-selection list boxes

When an HTML form contains either a list of check boxes with the same name or a multiple-selection list box (that is, where users can select multiple items from the list), 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 the user does not select a check box or make a selection from a list box, no variable is created. The cfinsert and cfupdate tags do not work correctly if there are no values. To correct this problem, make the form fields required, use Dynamic SQL, or use cfparam to establish a default value for the form field.

Check boxes

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

Handling numeric values

Suppose you want a user to select one or more departments using check boxes. You then query the database to retrieve detailed information on the selected department(s). The code for a simple set of check boxes that lets the user select departments looks like the following:

<input type="checkbox"

  name="SelectedDepts"
  value="1">
  Training<br>

<input type="checkbox"
  name="SelectedDepts"
  value="2">
  Marketing<br>

<input type="checkbox"
  name="SelectedDepts"
  value="3">
  HR<br>

<input type="checkbox"
  name="SelectedDepts"
  value="4">
  Sales<br>
</html>

The user sees the name of the department, but the value attribute of each check box is a number that corresponds to the underlying database primary key for the department's record.

If the user checks the Marketing and Sales items, the value of the SelectedDept form field is "2,4" and you use the SelectedDepts in the following SQL statement:

SELECT *
  FROM Departmt
  WHERE Dept_ID IN ( #Form.SelectedDepts# )

The ColdFusion Server sends the following statement to the database:

SELECT *
  FROM Departmt
  WHERE Dept_ID IN ( 2,4 )

Handling string values

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

The first example searched for department information based on a numeric primary key field called Dept_ID. Suppose, instead, that the primary key is a database field called Dept_Name that contains string values. In that case, your code for check boxes should look like the following:

<input type="checkbox"

  name="SelectedDepts"
  value="Training">
  Training<br>

<input type="checkbox"
  name="SelectedDepts"
  value="Marketing">
  Marketing<br>

<input type="checkbox"
  name="SelectedDepts"
  value="HR">
  HR<br>

<input type="checkbox"
  name="SelectedDepts"
  value="Sales">
  Sales<br>

If the user checked Marketing and Sales, the value of the SelectedDepts form field would be the list Marketing,Sales and you use the following SQL statement:

SELECT *
  FROM Departmt
  WHERE Dept_Name IN
  (#ListQualify(Form.SelectedDepts,"'")#)

Note:   In SQL, all strings must be surrounded in single quotes. The ListQualify function returns a list with the specified qualifying character (here, a single quote) around each item in the list.

If you select the second and fourth check boxes in the form, the following statement gets sent to the database:

SELECT *
  FROM Departmt
  WHERE Dept_Name IN ('Marketing','Sales')

Multiple selection lists

ColdFusion treats the result when a user selects multiple choices from a list box (HTML input type select with attribute multiple) just like results of selecting multiple check boxes. The data made available to your page from any multiple selection list box is a comma-delimited list of the entries selected by the user; for example, a list box could contain the four entries: Training, Marketing, HR, and Sales. If the user selects Marketing and Sales, the form field variable value is Marketing,Sales.

You use multiple selection lists to search a database in the same way that you use check boxes.

Handling numeric values

Suppose you want the user to select departments from a multiple-selection list box. The query retrieves detailed information on the selected department(s):

Select one or more companies to get more information on:

<select name="SelectDepts" multiple>
  <option value="1">Training
  <option value="2">Marketing
  <option value="3">HR
  <option value="4">Sales
</select>

If the user selects the Marketing and Sales items, the value of the SelectDepts form field is 2,4. If this parameter is used in the following SQL statement:

SELECT *
  FROM Departmt
  WHERE Dept_ID IN (#form.SelectDepts#)

the following statement is sent to the database:

SELECT *
  FROM Departmt
  WHERE Dept_ID IN (2,4)

Handling string values

Suppose you want the user to select departments from a multiple selection list box. The database search field is a string field. The query retrieves detailed information on the selected department(s):

<select name="SelectDepts" multiple>

  <option value="Training">Training
  <option value="Marketing">Marketing
  <option value="HR">HR
  <option value="Sales">Sales
</select>

If the user selects the Marketing and Sales items, the SelectDepts form field value is Marketing,Sales.

Just as you did when using check boxes to search database fields containing string values, use the ColdFusion ListQualify function with multiple-selection list boxes:

SELECT * 
  FROM Departmt
  WHERE Dept_Name IN (#ListQualify(Form.SelectDepts,"'")#) 

The following statement is sent to the database:

SELECT *
  FROM Departmt
  WHERE Dept_Name IN ('Marketing','Sales')

Copyright © 2002, Macromedia Inc. All rights reserved.

Comments