This chapter explains how to use CFML to dynamically populate forms and dynamically generate SQL.
Contents
• Dynamically Populating Select Boxes ... 46
• Creating Dynamic Checkboxes and Multiple Select Boxes ... 47
• Testing for a variable’s existence... 51
• Creating Default Variables with CFPARAM... 51
• Checking Query Parameters with CFQUERYPARAM ... 52
Dynamically Populating Select Boxes
In the previous chapter, you hard-coded a form’s select box options.
Instead of manually entering the information on a form, you can dynamically populate a select box with database fields. When you code this way, changes that you make to a database are automatically reflected on the form page.
To dynamically populate a select box:
• Use the CFQUERY tag to retrieve the column data from a database table.
• Use the CFOUTPUT tag with the QUERY attribute within the SELECT tag to dynamically populate the OPTIONS of this form control.
To dynamically populate a select box:
1. Open the file formpage.cfm in Studio. 2. Modify the file so that it appears as follows:
<HTML> <HEAD>
<TITLE>Input form</TITLE> </HEAD>
<BODY>
4 <CFQUERY NAME="GetDepartments" DATASOURCE="CompanyInfo"> 4 SELECT Location
4 FROM Departments 4 </CFQUERY>
<!--- define the action page in the form tag. The form variables will pass to this page when the form is submitted --->
<form action="actionpage.cfm" method="post"> <!-- text box -->
<P>
First Name: <INPUT TYPE="Text" NAME="FirstName" SIZE="20" MAXLENGTH="35"><BR>
Last Name: <INPUT TYPE="Text" NAME="LastName" SIZE="20" MAXLENGTH="35"><BR>
Salary: <INPUT TYPE="Text" NAME="Salary" SIZE="10" MAXLENGTH="10"> </P> <!-- select box --> City 4 <SELECT NAME="City"> 4 <CFOUTPUT QUERY="GetDepartments"> 4 <OPTION VALUE="#GetDepartments.Location#> 4 #GetDepartments.Location# 4 </OPTION> 4 </CFOUTPUT>
Chapter 5: Making Variables Dynamic 47
4 </SELECT>
<!-- radio buttons --> <P>
Department:<BR>
<INPUT TYPE="radio" name="Department" value="Training">Training<BR> <INPUT TYPE="radio" name="Department" value="Sales">Sales<BR>
<INPUT TYPE="radio" name="Department" value="Marketing">Marketing<BR> </P>
<!-- check box --> <P>
Contractor? <input type="checkbox" name="Contractor" value="Yes" checked>Yes
</P>
<!-- reset button -->
<INPUT TYPE="reset" NAME="ResetForm" VALUE="Clear Form"> <!-- submit button -->
<INPUT TYPE="submit" NAME="SubmitForm" VALUE="Submit"> </FORM>
</BODY> </HTML>
3. Save the page as formpage.cfm. 4. View formpage.cfm in a browser.
The changes that you just made appear in the form. Remember that you need an action page to submit values.
Creating Dynamic Checkboxes and Multiple Select Boxes
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..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. The values can be either numeric values or alphanumeric strings. These two types of values are treated slightly differently.
Searching numeric values
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 )
Searching string values
To search for a database field containing string values (instead of numeric), you must modify both the checkbox and CFQUERY syntax.
Chapter 5: Making Variables Dynamic 49
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:
• Make the value attribute of the checkboxes equal to the string value.
• Enclose the value attribute in single quotes. <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’)
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 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.
Searching numeric 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)
Searching string values
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
Chapter 5: Making Variables Dynamic 51
<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’)
Testing for a variable’s existence
Before relying on a variable’s existence in an application page, you can test to see if it exists using the IsDefined function. For example, the following code checks to see if a Form variable named Order_ID exists:
<CFIF Not IsDefined("FORM.Order_ID")> <CFLOCATION URL="previous_page.cfm"> </CFIF>
The argument passed to the IsDefined function must always be enclosed in double quotes. See the CFML Language Reference for more information on the IsDefined function.
If you attempt to evaluate a variable that has not been defined, ColdFusion will not be able to process the page. To help diagnose such problems, use the interactive debugger in ColdFusion Studio or turn debugging on in the ColdFusion Administrator. The Administrator debugging information shows which variables are being passed to your application pages.
Creating Default Variables with CFPARAM
Another way to create a variable is to test for its existence and optionally supply a default value if the variable does not already exist. The following shows the syntax of the CFPARAM tag:
<CFPARAM NAME="VariableName" TYPE="data_type"
There are two ways to use the CFPARAM tag, depending on how you want the validation test to proceed.
• Use CFPARAM with only the NAME attribute to test that a required variable exists. If it does not exist, the ColdFusion server stops processing the page.
• Use CFPARAM with both the NAME and DEFAULT attributes to test for the existence of an optional variable. If the variable exists, processing continues and the value is not changed. If the variable does not exist, it is created and set to the value of the DEFAULT attribute.
The following example shows how to use the CFPARAM tag to check for the existence of an optional variable and to set a default value if the variable does not already exist: <CFPARAM NAME="Form.Contract" DEFAULT="Yes">
Example: Testing for variables
Using CFPARAM with the NAME variable is a way to clearly define the variables that a page or a custom tag expects to receive before processing can proceed. This can make your code more readable, as well as easier to maintain and to debug.
For example, the following series of CFPARAM tags indicates that this page expects two form variables named StartRow and RowsToFetch:
<CFPARAM NAME="Form.StartRow"> <CFPARAM NAME="Form.RowsToFetch">
If the page with these tags is called without either one of the form variables, an error occurs and the page stops processing.
Example: Setting default values
In this example, CFPARAM is used to see if optional variables exist. If they do exist, processing continues. If they do not exist, they are created and set to the DEFAULT value.
<CFPARAM NAME="Cookie.SearchString" DEFAULT="temple"> <CFPARAM NAME="Client.Color" DEFAULT="Grey">
<CFPARAM NAME="ShowExtraInfo" DEFAULT="No">
You can also use CFPARAM to set default values for URL and Form variables, instead of using conditional logic.
Checking Query Parameters with CFQUERYPARAM
You can use the CFQUERYPARAM tag to check data types of query parameters and perform data validation.
Chapter 5: Making Variables Dynamic 53
Example: Checking data types
<!--- This example shows the use of CFQUERYPARAM when valid input is given in Dept_ID. ---> <HTML> <HEAD> <TITLE>CFQUERYPARAM Example</TITLE> </HEAD> <BODY> <H3>CFQUERYPARAM Example</H3> <CFSET Course_ID=12>
<CFQUERY NAME="getFirst" DataSource="CompanyInfo"> SELECT *
FROM departments
WHERE Dept_ID=<CFQUERYPARAM VALUE="#Dept_ID#" CFSQLTYPE="CF_SQL_INTEGER">
</CFQUERY>
<CFOUTPUT QUERY="getFirst"> <p>Department Number: #number#<br> Description: #descript# </P> </CFOUTPUT> </BODY> </HTML>
Dynamic SQL
Embedding SQL queries that use dynamic parameters is a powerful mechanism for linking variable inputs to database queries. However, in more sophisticated applications, you will often want user inputs to determine not only the content of queries but also the structure of queries.
Dynamic SQL allows you to dynamically determine (based on runtime parameters) which parts of a SQL statement are sent to the database. So if a user leaves a search field empty, for example, you could simply omit the part of the WHERE clause that refers to that field. Or, if a user does not specify a sort order, the entire ORDER BY clause could be omitted.
Dynamic SQL is implemented in ColdFusion by using CFIF, CFELSE, CFELSEIF tags to control how the SQL statement is constructed, for example:
<CFQUERY NAME="queryname" DATASOURCE="datasourcename">
...Base SQL statement
<CFIF value operator value > ...additional SQL
</CFIF> </CFQUERY>
First, you need to create an input form, which asks for information about several fields in the Employees table. Instead of entering information in each field, a user may want to search on certain fields, or even on only one field. To search for data based on only the fields the user enters in the form, you use CFIF statements in the SQL statement.
To create the input form:
1. Create a new application page in Studio. 2. Enter the following code:
<HTML> <HEAD>
<TITLE>Input form</TITLE> </HEAD>
<BODY>
<!--- Query the Employees table to be able to populate the form ---> <CFQUERY NAME="AskEmployees" DATASOURCE="CompanyInfo">
SELECT FirstName, LastName, Salary, Contract FROM Employees </CFQUERY>
<!--- define the action page in the form tag. The form variables will pass to this page when the form is submitted --->
<FORM ACTION="getemp.cfm" METHOD="post"> <!-- text box -->
<P>
First Name: <INPUT TYPE="Text" NAME="FirstName" SIZE="20" MAXLENGTH="35"><BR>
Last Name: <INPUT TYPE="Text" NAME="LastName" SIZE="20" MAXLENGTH="35"><BR>
Salary: <INPUT TYPE="Text" NAME="Salary" SIZE="10" MAXLENGTH="10"> </P>
<!-- check box --> <P>
Contractor? <input type="checkbox" name="Contract" value="Yes" >Yes if checked
Chapter 5: Making Variables Dynamic 55
<!-- reset button -->
<INPUT TYPE="reset" NAME="ResetForm" VALUE="Clear Form"> <!-- submit button -->
<INPUT TYPE="submit" NAME="SubmitForm" VALUE="Submit"> </FORM>
</BODY> </HTML>
3. Save the page as askemp.cfm.
Once you have created the input form, you can then create the action page to process the user’s request. This action page will determine where the user has entered search criteria and search based only on those criteria.
To create the action page:
1. Create a new application page in Studio. 2. Enter the following code:
<HTML> <HEAD>
<TITLE>Get Employee Data</TITLE> </HEAD>
<BODY>
<CFQUERY NAME="GetEmployees" DATASOURCE="CompanyInfo">
4 SELECT *
4 FROM Employees
4 WHERE 0=0
4
4 <CFIF #Form.FirstName# is not "">
4 AND Employees.FirstName LIKE ’#form.FirstName#%’ 4 </CFIF>
4 <CFIF #Form.LastName# is not "">
4 AND Employees.LastName LIKE ’#form.LastName#%’ 4 </CFIF>
4 <CFIF #Form.Salary# is not ""> 4 AND Employees.Salary >= #form.Salary# 4 </CFIF>
4 <CFIF isDefined("Form.Contract") IS "YES"> 4 AND Employees.Contract = ’Yes’
4 <CFELSE>
4 AND Employees.Contract = ’No’ 4 </CFIF>
</CFQUERY>
<TABLE> <TR> <TH>First Name</TH> <TH>Last Name</TH> <TH>Salary</TH> <TH>Contractor</TH> </TR> <CFOUTPUT QUERY="GetEmployees"> <TR> <TD>#FirstName#</TD> <TD>#LastName#</TD> <TD>#DollarFormat(Salary)#</TD> <TD>#Contract#</TD> </TR> </CFOUTPUT> </TABLE> </BODY> </HTML>
3. Save the page as getemp.cfm.
4. Open the file askemp.cfm in your browser and enter criteria into any fields, then submit the form.
5. The results should meet the criteria you specify.
Code Review
The action page getemp.cfm build a SQL statement dynamically based on what the user enters in the form page AskEmp.cfm.
CFML Code Description
SELECT *
FROM Employees WHERE 0=0
Get all the records from the Employees table as long as 0=0.
The WHERE 0=0 clause has no impact on the query submitted to the database. But if none of the conditions is true, it ensures that the WHERE clause does not result in a SQL syntax error.
<CFIF #Form.FirstName# is not ""> AND Employees.FirstName LIKE ’#form.FirstName#%’
</CFIF>
If the user entered anything in the FirstName text box in the form, add "AND Employees.FirstName LIKE ‘[what the user entered in the FirstName text box]%'" to the SQL statement.
Chapter 5: Making Variables Dynamic 57
<CFIF #Form.LastName# is not ""> AND Employees.LastName LIKE ’#form.LastName#%’
</CFIF>
If the user entered anything in the LastName text box in the form, add "AND Employees.LastName LIKE ‘[what the user entered in the LastName text box]%'" to the SQL statement.
<CFIF #Form.Salary# is not ""> AND Employees.Salary >= #form.Salary#
</CFIF>
If the user entered anything in the Salary text box in the form, add "AND
Employees.Salary >= [what the user entered in the Salary text box]" to the SQL statement.
<CFIF isDefined("Form.Contract") IS "YES">
AND Employees.Contract = ’Yes’ <CFELSE>
AND Employees.Contract = ’No’ </CFIF>
If the user checked the Contractor check box, get data for the employees who are