ColdFusion® MX: From Static to Dynamic in 10 Steps By Barry Moore
Table of Contents
Step 3. Databases and SQL
In this example, we are going to build a menu page to help users list employees by department. To do this, we will need a menu of departments for the user to choose from. When the user chooses a specific department, we will pass the DepartmentID number as a URL variable. We will then use that URL variable in our WHERE clause to pull employees from just that department.
Open your text editor and type the code shown in Listing 3.2, or you can open the EmployeeMenu.cfm file from the CompletedFiles\Examples\Step02 folder.
Listing 3.2 EmployeeMenu.cfm
<HTML> <HEAD> <TITLE>Employee Search Page</TITLE> </HEAD> <BODY> <H2>List Employees</H2> <P>List <A HREF="EmployeeList.cfm">all employees</A></P> <P>List Employees By Department<BR> <!--- send departmentID number as a URL variable ---> <A HREF="EmployeesByDept.cfm?Dept=2">Marketing</A><BR> <A HREF="EmployeesByDept.cfm?Dept=4">Operations</A><BR> <A HREF="EmployeesByDept.cfm?Dept=1">Sales</A><BR> <A HREF="EmployeesByDept.cfm?Dept=3">Technology</A><BR> </P> </BODY> </HTML>
Save this file as EmployeeMenu.cfm into you Examples\Step03 folder.
Make a copy of the EmployeeList.cfm template and rename it EmployeesByDept.cfm.
At the top of the EmployeesByDept.cfm template, change the comments and query to the following:
<!--- retrieve employees from a specific department ---> <CFQUERY NAME="qStaffList" DATASOURCE="Staff"> SELECT FirstName, LastName, Extension, DepartmentID FROM Employees WHERE DepartmentID = #URL.Dept# ORDER BY LastName </CFQUERY>
We will use the URL.Dept variable that has been passed from the EmployeeMenu.cfm page in our WHERE clause to restrict the results to a single department.
Save your changes and browse to the EmployeeMenu.cfm page.
Choose the Sales link. Your results should look similar to Figure 3.11.
