Building Flexible Search Interfaces

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.

Usage example

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="HRApp"> 
    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'
    </CFIF>
</CFQUERY>

• The CFIF tag tests to see if the Form.Department_Name variable is not an empty character string.
• If the test returns true, the SQL statement would include the AND clause.
• If the test returns not true, the AND clause is not included in the SQL statement.
• To test for multiple conditions, wrap additional CFIF tags around additional AND clauses.

To build a flexible search interface:

1. Open SearchForm.cfm.
2. Rename the title to Chapter 9 Employee Search Form.
3. Change the FORM tag's ACTION attribute to SearchAction.cfm.
4. Close and save the page.
5. Create a new application page in HomeSite.
6. Save the page as SearchAction.cfm within the CFDocs directory.

Naming is important because SearchForm.cfm passes variables to SearchAction.cfm when the form is submitted.

7. Title the page Chapter 9 Employee Search Results.
8. Include Toolbar.cfm directly beneath the BODY tag:

<CFINCLUDE TEMPLATE="Toolbar.cfm">

9. Directly beneath the CFINCLUDE tag, create a query named GetEmployees to retrieve data using the search criteria passed from the SearchForm.cfm page:

<CFQUERY NAME="GetEmployees" DATASOURCE="HRExpress">
</CFQUERY>

10. Add a SELECT statement within the query block to retrieve the FirstName, LastName, StartDate, Salary, Contract, and Department_Name columns from the Employees and Departments tables:

SELECT Employees.FirstName,
Employees.LastName, Employees.StartDate,
Employees.Salary, Employees.Contract,
Departments.Department_Name
FROM Employees, Departments

11. Directly beneath the FROM statement, join the tables together using the common column Department_ID:

WHERE Departments.Department_ID = Employees.Department_ID

12. Add a nested CFIF block directly after the WHERE clause to test if Form.LastName is defined and if it has a working value. If both are true, add an AND clause with pattern matching to include Form.LastName as search criteria:

<CFIF IsDefined("Form.LastName") IS "YES">
    <CFIF Form.LastName IS NOT "">
    AND Employees.LastName LIKE '%#Form.LastName#%'
    </CFIF>
</CFIF>

13. Add a nested CFIF block to the query to test if Form.Department_Name is defined. If it is, and if the user is searching on a single department rather than all departments, add an AND clause to include Form.Department_Name as search criteria:

<CFIF IsDefined("Form.Department_Name") IS "YES">
    <CFIF Form.Department_Name IS NOT "ALL">
    AND Departments.Department_Name='#Form.Department_Name#'
    </CFIF>
</CFIF>

14. Add another CFIF block to the query to test if Form.Contract exists, if so, add an AND clause to include Form.Contract as search criteria:

<CFIF IsDefined("Form.Contract") IS "YES">
AND Employees.Contract='#Form.Contract#'
</CFIF>

15. Save the page.
16. Test the search interface in your browser.

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.

Click here to run a search from SearchForm.cfm.

Click here to see SearchAction.cfm's code.

Move on to the next procedure to display the search results to users.

Copyright © 1999, Allaire Corporation. All rights reserved.