Building Search Interfaces

This chapter describes how to build form and action pages to search for and retrieve information stored in databases. During chapter practices, you will create a search interface that returns information based on search criteria.

Contents

Understanding Search Interfaces 98
Dynamically Generating SQL Statements 98
Filtering Data 99
Performing Pattern Matching 99
Filtering Data Based on Multiple Conditions 99
Creating Table Joins 100
Building Flexible Search Interfaces 101
Returning Results to the User 103
Development Considerations 105

Understanding Search Interfaces

Search interfaces allow you to use form input as search criteria on an action page to limit what’s retrieved from a database and displayed to the user.

The form and action pages that you build for search interfaces usually include the following code:

Dynamically populated form controls so that users can select
existing database information as search criteria.

For example, the form that you’re building dynamically populates a dropdown select box with data retreived from the Departments table.

Refer to Chapter 7, “Using Forms and Action Pages,” on page 71 to learn how to dynamically populate form controls.

Dynamically generated SQL statements so that user input on the form
will be used by the SELECT statement on the action page.

For example, the action page that you will build in this chapter will refer to user input to filter what is returned to the page during the query.

Dynamically Generating SQL Statements

As you learned in Chapter 5, “Building Pages that Retrieve Data,” on page 45, you can retrieve a record for every employee in a database table by composing a query like this:

SELECT FirstName, LastName, StartDate, Salary, Contract FROM Employees

</CFQUERY>

But when you want to return information about employees that match user search criteria , you would add the following to an action page query:

The SQL WHERE clause to filter what is returned from a database

query
The SQL LIKE operator with a wildcard string in a WHERE clause to

perform pattern matching
The SQL AND clause to the WHERE clause to filter what is returned

based on whether both conditions are true
A table join to filter data returned from multiple database tables
Conditional logic around AND clauses to create a flexible search

interface

Filtering Data

Use the SQL WHERE clause with a SQL SELECT statement to compare a value against a character string field. When the WHERE clause is processed, it filters the query data based on the results of the comparison.

Usage example

For example, to return employee data for only employees with the last name of Allaire, you would build a query that looks like this:

SELECT FirstName, LastName, StartDate, Salary, Contract FROM Employees

WHERE LastName = ’Allaire’

</CFQUERY>

Add the WHERE clause after the FROM clause.
Surround strings in SQL commands with single quotes (’) because SQL

is type sensitive.
The equal sign (=) sets up the comparison between the value on the

left with the value on the right.
You can use mulitple comparison operators in an expression.

Note The purpose of this guide is to get you up and running with ColdFusion Express. Allaire suggests that you purchase a book on SQL to learn how to use it efficiently.

Performing Pattern Matching

Use the SQL LIKE operator and SQL wildcard strings in a SQL WHERE clause when you want to compare a value against a character string field so that the query returns database information based on commonalities. This is known as pattern matching and is a very common search scenario.

Surround strings in SQL commands with single quotes (’).

Filtering Data Based on Multiple Conditions

Combine a SQL WHERE clause with a SQL AND clause in your queries when you only want to retrieve data based on the results of more than one comparison.

Usage example

For example, to return data for contract employees who earn more than $50,000, you would build a query that looks like this:

SELECT FirstName, LastName StartDate, Salary, Contract FROM Employees

WHERE Contract = ’Yes’ AND Salary > 50000

</CFQUERY>

Surround strings in SQL commands with single quotes (’).
When comparing a value against a numeric field, don't surround the

value with single quotes (’).

Creating Table Joins

Many times, the data that you want to retrieve is maintained in multiple tables. For example, in the database that you’re working with:

Department information is maintained in the Departments table. This

includes department ID numbers.
Employee information is maintained in the Employees table. This also

includes department ID numbers.

To compare and retrieve data from more than one table during a query, use the WHERE clause to join two tables through common information.

Usage example

For example, to return employee names, start dates, department names, and salaries for employees that work for the HR department, you would build a query that looks like this:

SELECT Departments.Department.Name, Employees.FirstName, Employees.LastName, Employees.StartDate, Employees.Salary

FROM Departments, Employees

WHERE Departments.Department_ID = Employees.Department_ID AND Departments.Department_Name = ’HR’

</CFQUERY>

Prefix each column in the SELECT statement to explicitly state which

table the data should be retrieved from.
The Department_ID field is the primary key of the Deparments table

and the Foreign Key of the Employees table.

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:

SELECT Departments.Department.Name, Employees.FirstName, Employees.LastName, Employees.StartDate, Employees.Salary

FROM Departments, Employees

WHERE Departments.Department_ID = Employees.Department_ID

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:

Open SearchForm.cfm.
Rename the title to Chapter 9 Employee Search Form.
Change the FORM tag’s ACTION attribute to SearchAction.cfm.
Close and save the page.
Create a new application page in HomeSite.
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.

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

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

</CFQUERY>

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

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

WHERE Departments.Department_ID = Employees.Department_ID

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:

AND Employees.LastName LIKE ’%#Form.LastName#%’

</CFIF>

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:

AND Departments.Department_Name=’#Form.Department_Name#’

</CFIF>

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>

Save the page.
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.

http://localhost/CFDOCS/exampleapp/TutorialSolutions/Chapter9/ SearchFormreturned.cfmClick here/a to run a search from SearchForm.cfm.

http://localhost/CFDOCS/exampleapp/TutorialSolutions/Chapter9/ SearchActionreturned.txtClick here/a to see SearchAction.cfm’s code.

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

Returning Results to the User

When you build search interfaces, keep in mind that there won't always be a record returned. If there is at least one record returned from a query, you will usually format that data using an HTML table. But to make sure that a search has retrieved records, you will need to test if any records have been returned using the recordcount variable in a conditional logic expression in order to display search results appropriately to users.

Usage example

For example, this code would be placed after the query to:

Return a message to the user that no records were found if the

number of records returned for the GetEmployees query is 0.
Display the data to the user if records are contains in the dataset.

No records match your search criteria. <br>

Please click<b> Search </b>on the toolbar and try again.

<TR>

<TH ALIGN="LEFT">First Name</TH>

<TH ALIGN="LEFT">Department</TH>

<TH ALIGN="LEFT">Start Date</TH>

<TH ALIGN="LEFT">Salary</TH>

<TH ALIGN="LEFT">Status</TH>

</TR>

<TR>

<TD>#GetEmployees.FirstName#</TD>

<TD>#GetEmployees.LastName#</TD>

<TD>#GetEmployees.Department_Name#</TD>

<TD>#DateFormat(GetEmployees.StartDate)#</TD>

<TD>#DollarFormat(GetEmployees.Salary)#</TD>

<TD><CFIF Contract IS "Yes">Contract

<CFELSE>Permanent</CFIF></TD>

</TR>

</CFOUTPUT>

</TABLE>

Prefix Recordcount with the queryname.
Add a true procedure that displays a message to the user.
Add a not true procedure after the CFELSE tag to format the returned

data using an HTML table.

To return search results to users:

Return to SearchAction.cfm in HomeSite.
Directly under the closing CFQUERY tag, add a page heading:

<H4>Employee Search Results</H4>

Begin a conditional logic expression to test whether no records are
returned from the query. If the test is true, return a message to the user:

No records match your search criteria. <BR> Please click searh button and try again.

Add a false procedure to the conditional logic expression that
outputs the query results to the user, formatted in a table:

<TR>

<TD>#GetEmployees.FirstName#</TD>

<TD>#GetEmployees.LastName#</TD>

<TD>#GetEmployees.Department_Name#</TD>

<TD>#DateFormat(GetEmployees.StartDate)#</TD>

<TD>#DollarFormat(GetEmployees.Salary)#</TD>

<TD><CFIF Contract IS "Yes"> Contract

<CFELSE>Permanent</CFIF></TD>

</TR>

</CFOUTPUT>

</TABLE>

Add table headers directly above the begin CFOUTPUT tag:

<TR>

<TH ALIGN="LEFT">First Name</TH>

<TH ALIGN="LEFT">Department</TH>

<TH ALIGN="LEFT">Start Date</TH>

<TH ALIGN="LEFT">Salary</TH>

<TH ALIGN="LEFT">Status</TH>

</TR>

End the conditional logic expression after the end TABLE tag:

</CFIF>

Save the page.
View EmpList.cfm in a browser.
Click on the SEARCH hyperlink in the toolbar.
Enter search criteria and SUBMIT the form.

The SELECT statement on SearchAction.cfm returns different results based on the criteria that you submit.

Before beginning the next chapter, move on to the summary to review development considerations.

Development Considerations

During this chapter, you learned about:

Search inteface characteristics
Dynamically generating SQL statements using the form variables

passed to an action page
A variety of SQL clauses, operators, and wildcards that you can use

to perform pattern matching and table joins during data retrieval
How to incorporate ColdFusion conditional logic into your SQL

statements to provide a truly flexible search interface
How to incorporate ColdFusion conditional logic and the RecordCount

variable into your action page in order to display search results back to users

Where to go from here

Refer to Chapter 6, “Formatting and Manipulating Data,” on page 61

to learn about working with HTML tables.
Move on to the next chapter to build a Web front-end.
Purchase a primer to learn about SQL.

106 Developing Web Applications with ColdFusion Express

C HA PT ER 10