Download Developing Web Applications with ColdFusion

Chapter 9: Building Search Interfaces
9.
143
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.
Move on to the next procedure to display the search results to users.