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, 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, use Dynamic SQL, or use CFPARAM to establish a default value for the form field.. |
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. The values can be either numeric values or alphanumeric strings. These two types of values are treated slightly differently.
Suppose you want a user to select one or more departments using checkboxes. You query the database to retrieve detailed information on the selected department(s).
Select one or more departments to get information on: <INPUT TYPE="checkbox" NAME="SelectedDept" VALUE="1"> Training<BR> <INPUT TYPE="checkbox" NAME="SelectedDept" VALUE="2"> Marketing<BR> <INPUT TYPE="checkbox" NAME="SelectedDept" VALUE="3"> HR<BR> <INPUT TYPE="checkbox" NAME="SelectedDept" VALUE="4"> Sales<BR> <INPUT TYPE="hidden" NAME="SelectedDepts_required" VALUE="You must select at least one organization.">
The text displayed to the user is the name of the department, but the VALUE attribute of each checkbox corresponds to the underlying database primary key for the department's record.
If the user checked the Marketing and Sales items, the value of the SelectedDept form field would be "2,4." If this parameter were used, the following would be the resulting SQL statement:
SELECT * FROM Departments WHERE Department_ID IN ( #form.SelectedDept# )
The statement sent to the database would be:
SELECT * FROM Departments WHERE Department_ID IN ( 2,4 )
To search for a database field containing string values (instead of numeric), you must modify both the checkbox and CFQUERY syntax.
The first example searched for department information based on a numeric primary key field called "Department_ID." Suppose, instead, that the primary key is a database field called "DepartmentName" that contains string values. In that case, it's necessary to make the following modifications:
<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> <INPUT TYPE="checkbox" NAME="SelectedDepts_required" VALUE="You must select at least one organization.">
If the user checked Marketing and Sales, the value of the SelectedDepts form field would be 'Marketing','Sales'.
Note | 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 Departments WHERE DepartmentName IN (#PreserveSingleQuotes(form.SelectedDepts)#)
The statement sent to the database would be:
SELECT * FROM Departments WHERE DepartmentName IN ('Marketing','Sales')
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 four entries: Training, Marketing, HR, and Sales. The user selects Marketing and Sales. The value of the form field variable is then 'Marketing', 'Sales'.
Just as you can with checkboxes, you can also query with multiple select lists by searching a database field that contains either numeric values or string values.
For example, suppose you want the user to select departments from a multiple select 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> <INPUT TYPE="hidden" NAME="SelectDepts_required" VALUE="You must select at least one department.">
If the user selected the Marketing and Sales items, the value of the SelectDepts form field would be 2,4.
If this parameter were used in the following SQL statement:
SELECT * FROM Departments WHERE Department_ID IN (#form.SelectDepts#)
the statement sent to the database would be:
SELECT * FROM Departments WHERE Department_ID IN (2,4)
Suppose you want the user to select departments from a multiple select list. The database field search is a string field. The query retrieves detailed information on the selected department(s):
Select one or more departments to get more information on: <SELECT Name="SelectDepts" MULTIPLE> <OPTION VALUE="'Training'">Training <OPTION VALUE="'Marketing'">Marketing <OPTION VALUE="'HR'">HR <OPTION VALUE="'Sales'">Sales </SELECT> <INPUT TYPE="hidden" NAME="SelectDepts_required" VALUE="You must select at least one department.">
If the user selected the Marketing and Sales items, the value of the SelectDepts form field would be 'Marketing','Sales'.
Just as you did when using checkboxes to search database fields containing string values, use the ColdFusion PreserveSingleQuotes function with multiple select boxes:
SELECT * FROM Departments WHERE DepartmentName IN (#PreserveSingleQuotes(form.SelectDepts)#)
The statement sent to the database would be:
SELECT * FROM Departments WHERE DepartmentName IN ('Marketing', 'Sales')