Building flexible search interfaces

One option with forms is to build a search based on the form data. For example, you could use form data as part of the WHERE clause to construct a database query.

To give users the option to enter multiple search criteria in a form, you can wrap conditional logic around a SQL AND clause as part of the WHERE clause. The following action page allows users to search for employees by department, last name, or both.

Note: ColdFusion MX provides the Verity search utility that you can also use to perform a search. For more information, see Building a Search Interface.

To build a more flexible search interface:

  1. Open the ColdFusion actionpage.cfm page in your editor.
  2. Modify the page so that it appears as follows: 
    <html>
<head>
<title>Retrieving Employee Data Based on Criteria from Form</title>
</head>
<body>
<cfquery name="GetEmployees" datasource="cfdocexamples"> 
	SELECT Departmt.Dept_Name,
		Employee.FirstName,
		Employee.LastName,
		Employee.StartDate,
		Employee.Salary
	FROM Departmt, Employee
	WHERE Departmt.Dept_ID = Employee.Dept_ID
	<cfif IsDefined("Form.Department")>
    
AND Departmt.Dept_Name=<cfqueryparam value="#Form.Department#" 
    
			CFSQLType="CF_SQL_VARCHAR"> 
	</cfif>
	<cfif Form.LastName IS NOT "">
		AND Employee.LastName=<cfqueryparam value="#Form.LastName#" 
    
			CFSQLType="CF_SQL_VARCHAR">
	</cfif>
</cfquery>

<h4>Employee Data Based on Criteria 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>
  3. Save the file.
  4. View the formpage.cfm page in your browser.
  5. Select a department, optionally enter a last name, and submit the form.

Reviewing the code

The following table describes the highlighted code and its function:

Code Description 
SELECT  Departmt.Dept_Name,
   Employee.FirstName,
   Employee.LastName,
   Employee.StartDate,
   Employee.Salary
   FROM Departmt, Employee
   WHERE Departmt.Dept_ID =
      Employee.Dept_ID

Retrieves the fields listed from the Departmt and Employee tables, joining the tables based on the Dept_ID field in each table.

 
   <cfif IsDefined("FORM.Department")>
   AND Departmt.Dept_Name = <cfqueryparam 
      value="#Form.Department#" 
      CFSQLType="CF_SQL_VARCHAR">
   </cfif>

If the user specified a department on the form, only retrieves records where the department name is the same as the one that the user specified. You must use number signs (#) in the SQL AND statement to identify Form.Department as a ColdFusion variable, but not in the IsDefined function.

 
<cfif Form.LastName IS NOT "">
AND Employee.LastName = <cfqueryparam 
      value="#Form.LastName#" 
      CFSQLType="CF_SQL_VARCHAR">
</cfif>

If the user specified a last name in the form, only retrieves the records in which the last name is the same as the one that the user entered in the form.

View comments in LiveDocs