When you put a series of check boxes with the same name in a 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:

<cfinput type="checkbox"
    name="SelectedDepts"
    value="1">
    Training<br>

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

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

<cfinput 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 SelectedDepts form field is 2,4 and you use the SelectedDepts value 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 that contains string values (instead of numeric), you must modify the checkbox and cfquery syntax to make sure that the string values are sent to the data source in single-quotation marks (').

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:

<cfinput type="checkbox"
    name="SelectedDepts"
    value="Training">
    Training<br>

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

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

<cfinput 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,"'")#)

In SQL, all strings must be surrounded in single-quotation marks. The ListQualify function returns a list with the specified qualifying character (here, a single-quotation mark) 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')