Adobe ColdFusion 8

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 provides the Verity search utility that you can also use to perform a search. For more information, see Building a Search Interface.

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.