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.

ColdFusion 9 | ColdFusion 8 | ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | KnowledgeBase | Bug Reporting

Version 7

 

Send me an e-mail when comments are added to this page | Comment Report

Current page: http://livedocs.adobe.com/coldfusion/7/htmldocs/00001343.htm