Frequently, you will want users to optionally enter multiple search criteria.
Wrap conditional logic around the SQL AND clause to build a flexible search interface. To test for multiple conditions, wrap additional CFIF tags around additional AND clauses.
For example, to allow users to search for employees by last name, department, or both, you would build a query that looks like this:
<CFQUERY NAME="GetEmployees" DATASOURCE="CompanyInfo"> SELECT Departments.Department.Name, Employees.FirstName, Employees.LastName, Employees.StartDate, Employees.Salary FROM Departments, Employees WHERE 1=1 <CFIF Form.LastName IS NOT ""> AND Employees.LastName = 'Form.LastName' </CFIF> </CFQUERY>
Code | Description |
---|---|
SELECT Departments.Department.Name, Employees.FirstName, Employees.LastName, Employees.StartDate, Employees.Salary FROM Departments, Employees WHERE 1=1 | Retrieve the fields listed from the Departments and Employees tables, joining the tables based on the Department_ID field in each table. |
<CFIF Form.LastName IS NOT ""> AND Employees.LastName = 'Form.LastName' </CFIF> | But if the user specified a last name in the form, only retrieve the records where the last name is the same as the one the user entered in the form. |
![]() |
To build a flexible search interface: |
actionpage.cfm
in Studio.
<HTML> <HEAD> <TITLE>Retrieving Employee Data Based on Criteria from Form</TITLE> </HEAD> <BODY> <CFQUERY NAME="GetEmployees" DATASOURCE="CompanyInfo"> SELECT Departments.Department.Name, Employees.FirstName, Employees.LastName, Employees.StartDate, Employees.Salary FROM Departments, Employees WHERE Departments.Department_ID = Employees.Department_ID <CFIF Form.Department_Name IS NOT ""> AND Departments.Department_Name = 'Form.Department_Name' </CFQUERY> <H4>Employee Data Based on Criteia from Form</H4> <TABLE> <TR> <TH>First Name</TH> <TH>Last Name</TH>
<TH>Salary</TH> </TR> <CFOUTPUT QUERY="GetEmployees"> <TR> <TD>#FirstName#</TD> <TD>#LastName#</TD> <TD>#Salary#</TD> </TR> </CFOUTPUT> </TABLE> </BODY> </HTML>
The returned records will not be displayed because you have not entered that code yet, however, you will see the number of records returned if you have debugging enabled.