• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Step 3. Databases and SQL > Example 3.2: Listing Employees by Department

Example 3.2: Listing Employees by Department

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

    <TITLE>Employee Search Page</TITLE> 
         <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> 


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 ---> 
      SELECT     FirstName, LastName, Extension, DepartmentID 
      FROM       Employees 
      WHERE      DepartmentID = #URL.Dept# 
      ORDER BY   LastName 

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.

Figure 3.11. Search results by department.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint