Understanding the Power of Check Boxes and Multiple Select Lists ColdFusion Help

so far in this chapter you have seen simple examples of select fields in which a single item was chosen. However HTML select fields allow for multiple selections. In addition groups of check body allow for multiple items to be selected. For instance Figure 15.13·shows both types of fields with multiple items selected.

This ability to select multiple items can be particularly useful in ColdFusion. The reason for this is that when multiple items are selected by using either type of field the value associated with the field when the form is submitted is a comma-delimited list of the values of all selected entries

FIGURE 15.13

FIGURE 15.13

There are several ways to leverage the resulting data, including:

• Using the wide range of List functions available in ColdFusion. These functions are discussed in detail in Chapter 14, “Working with ColdFusion Data Structures.”
• Using the SQL “‘N operator to match multiple items in a comma-delimited list. Because working with lists is covered in such detail in Chapter 14, this chapter will focus on the second way to take advantage of multiple selections

First you need to review how to create multiple select fields and check boxes. The multiple select field is created by using the attribute MULTIPLE=’ YES’ as well as by setting the number of visible rows to a size greater than 1 using the SIZE attribute. For instance the following field displays five lines of the selection list and allows the selection of multiple elements in the list the following:

<SELECT NAME-‘Example’ MULTIPLE-‘Yes’ SIZE-S)
<OPTION)l
<OPTION)2
<OPTION) 3
<oPTION)4
<OPTION) 5
</SELECT>

By contrast a group of related check boxes is created using a series of INPUT tags with the same name:

<INPUT TYPE-checkbox NAME-‘EXAMPlE’ VAlUE-l> l<BR>
<INPUT TYPE-checkbox NAME-‘EXAMPlE’. VAlUE-2> 2<BR>
<INPUT TYPE-checkbox NAME-‘~PlE’ VAlUE-3> 3<BR>
<INPUT TYPE-checkbox NAME-‘EXAMPlE’ VAlUE-4> 4<8R>
<INPUT TYPE-checkbox NAME-‘EXAMPlE’ VALUE-5>.r 5

How, then, do you use these types of form fields to search for multiple records in a. database? Let’s turn again to the simple employee database. Consider an example in which you want to give users the choice of 10 employees whose names they can display. They should be able to select any number of IDs from a list and then submit the form to another template that proceeds to find all the names and display hem.

The template for displaying the form would look like this

<Hl>Select Employees</Hl>
<FORM METHOD-POST ACTION-display.cfm>
<SELECT NAME-‘IDS’ SIZE-10 MULTIPLE>
<OPTION> 1
<OPTION>2
<OPTION>3
<OPTION>4
<OP’-:::>O5
<OPTI0N>6
,<OPTION>7
<OPTION>8
.<OPTION>9
<OPTION>lO
</SELEClXBR>
<INPUT TYPE-SUBMIT>
</FORM>

This code produces a form like the one in Figure 15.14

After the user selects the desired employees and submits the form the following tern plate is executed. It uses the SQL IN operator. The IN operator has slightly different requirements with strings than with numbers; we will discuss numbers first

FIGURE 15.14

FIGURE 15.14

The IN operator is used in place of other comparison operators such as – when searching for one specific record by using SELECT statements. For instance to search your employee database for two records at once employee ID’1 and employee ID 2 you could use

SELECT *
FROM Employees
WHERE 10 IN ( 1,2 )

Notice that the ID numbers are in a comma-delimited list inside parentheses. Because your form returns a comma-delimited list, you can use ~ list inside the parentheses of the SELECT statement in your template:

<CFQijERYNAME-‘Results’ DATASOURCE-‘EmployeeOata’)
SELECT *
FROM -Emp1oyees
WHERE 10 in ( #Form.IDS# )
</CFQUERY>
<CFOUTPUT QUERY-‘Results’)
#LastName#, #FirstName#
<HR>
</CFOUTPUT>

This code produces results like those in Figure 15.15.

FIGURE 15.15

FIGURE 15.15

However working with string value~ requires a little extra attention. When using values of string fields in SQL, you need to wrap these values in single quotation marks. , This applies to the comma-delimited list with. the IN operator as well: ( ‘Value One’,’Value Two’,’Value Three’, etc.).

This means that if you want to build selection lists or check boxes to search for string values in a database you need to make sure that the single quotes are part of the form values so the comma-delimited list that gets submitted has the appropriate single quotation marks.

For instance if you want to select names to display on the basis of last names you might think that the following form would work:

<Hl>Select Employees</Hl>
<FORM METHOD-POST ACTION-display.cfm>
<SELECT NAME-‘Names’ SIZE-4 MULTIPLE-YES>
<OPTION>Danesh
<OPTION>Doe
<CiPTION>Johnson
<OPTION>Smith
</SELECT><8R>
<INPUT TYPE-SUBMIT>
</FORM>

But if you try to use this to search for employees using the following query you will get an error

<CFQUERY NAME-‘Results’ DATASOURCE-‘EmployeeData’>
SELECT *
FROM .Employees
WHERE LastName in ( #Form.Names# )
</CFQUERY>

To address this error you need to adjust your form so that the value submitted includes the single quotation marks but the value displayed doesn’t

<Hl>Select Employees</Hl>
<FORM METHOD-POST.ACTION-display.cfm>
<SELECT NAME-‘Names’ SIZE-4 MULTIPLE-YES>
<OPTION VALUE-” Danesh ”>Danesh
<OPTION VA.LUE-”Doe”>Ooe
# <OPTION VALUE-‘ ‘Johnson’ ‘)Johnson
<OPTION VALUE-“$mith”)$mith
</SElECT><BR)
<INPUT TYPE-SUBMIT>
</FORM>

You also need to use the t’t’eserveS; ngl eQuotes function when building the SQL statement; otherwise ColdFtision will escape the quotes and they will be lost in the SQL statement, leading to another error:

<CFQUERY NAME-‘Results’ DATASOURCE-‘EmployeeData’>
SELECT *
FROM Employees
WHERE LastName in ( #PreserveSingleQuotes(Fonm.Names)# )
</CFQUERY>

Posted on November 17, 2015 in Forms

Share the Story

Back to Top
Share This