User Guide

Chapter 4: Retrieving and Formatting the Data You Want 41

AND Employees.LastName = ’Form.LastName’

</CFIF>

</CFQUERY>

Code Review

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>

4 SELECT Departments.Department.Name,

4 Employees.FirstName,

4 Employees.LastName,

4 Employees.StartDate,

4 Employees.Salary

4 FROM Departments, Employees

4 WHERE Departments.Department_ID = Employees.Department_ID

4 <CFIF Form.Department_Name IS NOT "">

4 AND Departments.Department_Name = ’Form.Department_Name’

4 </CFQUERY>

<H4>Employee Data Based on Criteia from Form</H4>

<TABLE>

<TR>

<TH>First Name</TH>

<TH>Salary</TH>

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.

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.