![]() ![]() ![]() |
||
|
One option with forms is to build a search based on the form data. For example, you could use form data as part of the WHERE clause to construct a database query.
To give users the option to enter multiple search criteria in a form, you can wrap conditional logic around a SQL AND clause as part of the WHERE clause. The following action page allows users to search for employees by department, last name, or both.
Note: ColdFusion MX provides the Verity search utility that you can also use to perform a search. For more information, see Building a Search Interface.
<html> <head> <title>Retrieving Employee Data Based on Criteria from Form</title> </head> <body> <cfquery name="GetEmployees" datasource="cfdocexamples">SELECT Departmt.Dept_Name,
Employee.FirstName,
Employee.LastName,
Employee.StartDate,
Employee.Salary
FROM Departmt, Employee
WHERE Departmt.Dept_ID = Employee.Dept_ID
<cfif IsDefined("Form.Department")>
=<cfqueryparam value="#Form.Department#"
AND Departmt.Dept_Name
CFSQLType="CF_SQL_VARCHAR"></cfif>
<cfif Form.LastName IS NOT "">
AND Employee.LastName=
<cfqueryparam value="#Form.LastName
#"
CFSQLType="CF_SQL_VARCHAR"></cfif>
</cfquery> <h4>Employee Data Based on Criteria 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>
The following table describes the highlighted code and its function:
Code |
Description |
---|---|
SELECT Departmt.Dept_Name, Employee.FirstName, Employee.LastName, Employee.StartDate, Employee.Salary FROM Departmt, Employee WHERE Departmt.Dept_ID = |
Retrieves the fields listed from the Departmt and Employee tables, joining the tables based on the Dept_ID field in each table. |
<cfif IsDefined("FORM.Department")> AND Departmt.Dept_Name = <cfqueryparam |
If the user specified a department on the form, only retrieves records where the department name is the same as the one that the user specified. You must use number signs (#) in the SQL AND statement to identify Form.Department as a ColdFusion variable, but not in the |
<cfif Form.LastName IS NOT ""> AND Employee.LastName = <cfqueryparam |
If the user specified a last name in the form, only retrieves the records in which the last name is the same as the one that the user entered in the form. |
|
||
![]() ![]() ![]() |