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.