| We can mix ColdFusion in with our normal HTML forms. In this example, we are going to use a ColdFusion query to get a list of items from the database, and then we'll use those items to dynamically populate a <SELECT> menu on our form. This is very handy for long lists such as those used to select a state or a country. We are also going to build the beginnings of a search form to help users find employee information in our Staff.mdb database. Creating a Search Page First we need to create a menu to let us search for employees by department. -
Open your browser and type the code shown in Listing 5.3 or open the completed Form1.cfm file from the CompletedFiles\Examples\Step05 folder. Listing 5.3 Form1.cfm <!--- File: Form1.cfm Description: Simple form demo with dynamic select box 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 1</TITLE> </HEAD> <BODY> <H2>Employee Search By Department</H2> <FORM ACTION="Action1.cfm" METHOD="post"> Select A Department: <SELECT NAME="DepartmentID"> <!--- use query info to build list options ---> <CFOUTPUT QUERY="qDepartments"> <OPTION VALUE="#DepartmentID#">#DepartmentName#</OPTION> </CFOUTPUT> </SELECT> <INPUT TYPE="submit" VALUE="Search"> </FORM> </BODY> </HTML> -
Save the file as Form1.cfm in your Examples\Step05 folder. Browse to the page and test that the drop-down menu contains all the departments.
Figure 5.7. The dynamic select list browser display.  In this example, we are pulling a list of department names from the Departments table using a <CFQUERY> statement. Later, we start the code for a normal HTML <SELECT> menu. Instead of coding all the <OPTIONS> for the list by hand, we can use a <CFOUTPUT> block to loop through all the departments we retrieved from the database using the query. Clever, huh? When we submit the form, it will pass a single form variable whose name we specified in the NAME attribute of the <SELECT> tag. This is a great way to populate menus that contain long lists of items such as state abbreviations or country codes. Creating a Results Page Next we are going to build an action page to display results based on the department selected in our search page. This action page is shown in Figure 5.8. Figure 5.8. The search results browser display.  Because the actual department name (marketing, sales, and so on) is stored in the Departments table and all the other information we want (like first name, last name, and extension) is stored in the Employees table, we are going to have to query information from two tables at the same time. See the sidebar "Multitable Queries." Multitable Queries There will be many times when you need to pull database information from more than one table at a time. This is not a difficult feat as long as you know how the database is structured. In Step 3, "Databases and SQL," we mentioned that tables within a database often have a relationship to one another. For example, our Departments table has a one-to-many relationship with the Employees table. Tables are linked in these relationships by a common field, in this case the DepartmentID field. If you want to query information from several tables, you must know how the tables are joined. In the following example, we will pull our employees' first name, last name, and the name of their department. The first name and last name information will come from the Employees table, and the department name will come from the Departments table. We will also need to pull the DepartmentID field from both tables because this is the field that links both tables. [View full width] <CFQUERY NAME="qEmployees" DATASOURCE="Staff"> SELECT Employees.FirstName, Employees.LastName,Employees.DepartmentID,  Departments.DepartmentID, Departments.DepartmentName FROM Employees, Departments WHERE Employees.DepartmentID = Departments. DepartmentID ORDER BY LastName </CFQUERY>
In the preceding code, we specify the tables we want to draw information from by listing multiple table names after the SQL FROM keyword, and we separate the table names with a comma. Because we are pulling field information from multiple tables, we need to prefix each field name with the name of the table from which we are pulling it. We also use the WHERE clause to specify how the tables are joined. In this case, they are joined by the common field of DepartmentID. The only remaining problem is that we haven't actually specified which department information we want to see. We can do this by adding an additional comparison to our WHERE clause. We do this by using the SQL AND keyword. [View full width] <CFQUERY NAME="qEmployees" DATASOURCE="Staff"> SELECT Employees.FirstName, Employees.LastName, Employees.DepartmentID, Departments. DepartmentID, Departments.DepartmentName FROM Employees, Departments WHERE Employees.DepartmentID = 2 AND Employees.DepartmentID = Departments. DepartmentID ORDER BY LastName </CFQUERY>
In the preceding query, we have rearranged our WHERE clause a little and have added a line, using the AND keyword, to instruct ColdFusion to get records from the Employees table where the Employees.DepartmentID equals 2 (the value for the marketing department). Next, we use the AND keyword to specify we only want records from both tables where the value of Employees.DepartmentID (2) matches the value of Departments.DepartmentID (2). This will result in ColdFusion retrieving all employees in the Employees table who work in department 2 (marketing), as well as the DepartmentName from the Departments table where the DepartmentID equals 2 (in this case the department name, Marketing). You can use this technique to pull information from as many tables as you like, as long as you know how the tables are joined. |
-
Open your text editor and type the code in Listing 5.4 or open the completed Action1.cfm file from the CompletedFiles\Examples\Step05 folder. Listing 5.4 Action1.cfm [View full width] <!--- File: Action1.cfm Description: Action page to display search results Author: Created: ---> <!---query database using information passed by the form---> <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 = #FORM.DepartmentID# AND Employees.DepartmentID = Departments.DepartmentID ORDER BY LastName </CFQUERY> <HTML> <HEAD> <TITLE>Action Page 1</TITLE> </HEAD> <BODY> <H2>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="Form1.cfm">Back to Search</A></P> </BODY> </HTML> -
Save this file as Action1.cfm in your Examples\Step05 folder.
This file uses a multitable query to pull employee information from the Employees table and the department name from the Departments table, based of the value of the FORM.DepartmentID variable passed by the form page. Once the information has been retrieved from the database, it is simply a matter of using <CFOUTPUT> to loop through the query results. Notice that inside the <CFOUTPUT> block, there is a conditional statement around the <TR> code that opens each new table row. This code is used to display alternating rows with a different background color. See the sidebar "The Alternating Row Color Trick." The Alternating Row Color Trick A very popular technique used when displaying large tables of database information is to use alternating row colors to enhance readability. One way to accomplish this is to use a mathematical operator called MOD (short for "modulus"). The MOD operator basically finds the remainder left after dividing two integer numbers. For example, 15 divided by 6 equals 2 with a remainder of 3, so in ColdFusion #15 MOD 6.htm# would return a value of 3 (the remainder). In Example 5.1: A Simple Search Form, we retrieved some information from a database and outputted that information in a table. To get the table rows to alternate color, we can use an IF statement in conjunction with the MOD operator. <CFIF qEmployees.CurrentRow MOD 2 IS 0> <!--- even row color ---> <TR BGCOLOR="##CCFFFF"> <CFELSE> <!--- odd row color ---> <TR BGCOLOR="##FFFFFF"> </CFIF> The preceding code uses the MOD operator to divide the query's CurrentRow value (the row number that <CFOUTPUT> is currently looping through) by 2. If the remainder of that division is 0, the current row must be an even-numbered row, and we use the row's opening <TR> tag to assign it a particular color. If the remainder is not 0, the current row must be an odd-numbered row, and we assign it a different color. Presto, we now have alternating row colors. |
|
