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. To test for multiple conditions, wrap additional CFIF tags around additional AND clauses.

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="CompanyInfo"> 
    SELECT  Departments.Department.Name,
    Employees.FirstName,
    Employees.LastName,
    Employees.StartDate,
    Employees.Salary
    FROM Departments, Employees
    WHERE 1=1
    <CFIF Form.LastName IS NOT "">
    AND Employees.LastName = 'Form.LastName'
    </CFIF>
</CFQUERY>

Code Review
Code Description
SELECT  Departments.Department.Name,
    Employees.FirstName,
    Employees.LastName,
    Employees.StartDate,
    Employees.Salary
    FROM Departments, Employees
WHERE 1=1
Retrieve the fields listed from the Departments and Employees tables, joining the tables based on the Department_ID field in each table.
<CFIF Form.LastName IS NOT "">
AND Employees.LastName = 'Form.LastName'
</CFIF>
But if the user specified a last name in the form, only retrieve the records where the last name is the same as the one the user entered in the form.

Note To build a flexible search interface:
  1. Return to actionpage.cfm in Studio.
  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="CompanyInfo">
    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'
    </CFQUERY>
    <H4>Employee Data Based on Criteia 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. 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.