| In this example, we will build the form shown in Figure 5.9. We will add fields for users to enter first and last name information as well as the option to specify department information. -
Open you text editor and enter the code in Listing 5.5 or open the completed Form2.cfm file from the CompletedFiles\Examples\Step05 folder. Listing 5.5 Form2.cfm <!--- File: Form2.cfm Description: Multi-field search form Author: Created: ---> <!--- get department information for the select menu ---> <CFQUERY NAME="qDepartments" DATASOURCE="Staff"> SELECT DepartmentID, DepartmentName FROM Departments ORDER BY DepartmentName </CFQUERY> <HTML> <HEAD> <TITLE>HTML Form 2</TITLE> </HEAD> <BODY> <H2>Employee Search Form</H2> Search for an employee by entering their name,<BR> or the first few letters of their name. <BR> You can also specify a department if you wish <!--- specify Action2.cfm as the action page ---> <FORM ACTION="Action2.cfm" METHOD="post"> <!--- input box to enter first name information ---> First Name: <INPUT TYPE="text" NAME="SearchFName" SIZE="20"><BR> <!--- input box to enter last name information ---> Last Name: <INPUT TYPE="text" NAME="SearchLName" SIZE="20"><BR> Select A Department: <SELECT NAME="DepartmentID"> <!--- include a choice to search all departments ---> <OPTION VALUE="" SELECTED>All Depts</OPTION> <!--- use query info to build list options ---> <CFOUTPUT QUERY="qDepartments"> <OPTION VALUE="#DepartmentID#">#DepartmentName#</OPTION> </CFOUTPUT> </SELECT><BR> <INPUT TYPE="submit" VALUE="Search"> </FORM> </BODY> </HTML> -
Save this file into your Example\Step05 folder as Form2.cfm.
This form builds on the form we created in Example 5.1: A Simple Search Form. With this form, we have added two additional search input boxes in which users can enter first and last name information; these input boxes will pass FORM variables to our action page named SearchFName and SearchLName, respectively. We also have added another OPTION to our SELECT menu called "All Depts", which will pass an empty string ("") to our action page if the OPTION is chosen. Don't forget to make sure this form has an ACTION attribute of Action2.cfm. Next let's create the action page to display our search results. Because a user can enter any combination of up to three different search criteria, our action page needs to contain a dynamic query that can respond to all these possibilities. If a user clicks the search button without entering any search criteria, a list of all employees will be returned. In addition, we will use the Trim() function to get rid of any leading or trailing spaces from the variables that the user has entered. -
Open your text editor and type the code in Listing 5.6 or open the completed Action2.cfm file from the CompletedFiles\Examples\Step05 folder. Listing 5.6 Action2.cfm <!--- File: Action2.cfm Description: Action page to for Form2.cfm Author: Created: ---> <!--- get department information for the select menu ---> <CFQUERY NAME="qEmployees" DATASOURCE="Staff"> SELECT Employees.FirstName, Employees.LastName, Employees.DepartmentID, Employees.Title, Employees.Email, Employees.Extension, Departments.DepartmentID, Departments.DepartmentName FROM Employees, Departments WHERE Employees.DepartmentID = Departments.DepartmentID <!--- check to see if a department is specified if it is, include it in the search ---> <CFIF FORM.DepartmentID IS NOT "" > AND Employees.DepartmentID = #FORM.DepartmentID# </CFIF> <!--- check to see if the SearchFName box contains any text if it does, include it in the search ---> <CFIF FORM.SearchFname IS NOT ""> AND Employees.FirstName LIKE '#Trim(FORM.SearchFName)#%' </CFIF> <!--- check to see if the SearchLName box contains any text if it does, include it in the search ---> <CFIF FORM.SearchLName IS NOT ""> AND Employees.LastName LIKE '#Trim(FORM.SearchLName)#%' </CFIF> ORDER BY Employees.LastName </CFQUERY> <HTML> <HEAD> <TITLE>Action Page 2</TITLE> </HEAD> <BODY> <H2><CFOUTPUT>#qEmployees.RecordCount#</CFOUTPUT> Employee Search Results</H2> <TABLE WIDTH="90%" BORDER="0" CELLSPACING="0" CELLPADDING="2"> <!--- header row ---> <TR BGCOLOR="#ECECEC"> <TD><B>Last Name</B></TD> <TD><B>First Name</B></TD> <TD><B>Title</B></TD> <TD ALIGN="center"><B>Department</B></TD> <TD ALIGN="center"><B>Extension</B></TD> <TD><B>Email</B></TD> </TR> <!--- output the search results ---> <CFOUTPUT QUERY="qEmployees"> <!--- the alternating row color trick divide the current row number by 2 if there is no remainder (MOD IS 0) the row must be an even number---> <CFIF qEmployees.CurrentRow MOD 2 IS 0> <!--- even row color ---> <TR BGCOLOR="##CCFFFF"> <CFELSE> <!--- odd row color ---> <TR BGCOLOR="##FFFFFF"> </CFIF> <TD>#LastName#</TD> <TD>#FirstName#</TD> <TD>#Title#</TD> <TD ALIGN="center">#DepartmentName#</TD> <TD ALIGN="center">#Extension#</TD> <TD>#Email#</TD> </TR> </CFOUTPUT> </TABLE> <P><A HREF="Form2.cfm">Back to Search</A></P> </BODY> </HTML> -
Be sure the Back to Search link at the bottom of the page points back to Form2.cfm. -
Save the file to your Examples\Step05 folder as Action2.cfm. -
Browse to the Form2.cfm file and experiment with search criteria combinations. For example, try searching for "mo" in the last name field, as shown in Figure 5.10. Your action page should return the results shown in Figure 5.11. Then try entering no search criteria. Figure 5.10. Using the advanced search form. Figure 5.11. Advanced search results.
|