Example 3.1: Creating an Employee List
In this example, we are going to create a page that lists all of our
employees
. We would like to see their first names, last
names
, and extension
numbers
. We would also like to list them
alphabetically
by their last names.
NOTE
You can place your
<CFQUERY>
code
anywhere
in your template as long as it is before any
<CFOUTPUT>
block that
tries
to use the results. Many programmers choose to place all query code at the top of the template before any HTML. This way, queries are easy to locate, and you can be certain they will run before any
<CFOUTPUT>
blocks that might try to display the results. Some programming methodologies such as Fusebox also promote keeping your queries in separate files and using
<CFINCLUDE>
to include them in other templates where necessary.
First we will need some <CFQUERY> code to ask for the appropriate information.
-
Open
your text editor and create a new document. At the very top of the document before the opening HTML tag, type the following code:
<!--- retrieve all employees and extensions --->
<CFQUERY NAME="qStaffList" DATASOURCE="Staff">
SELECT FirstName, LastName, Extension
FROM Employees
ORDER BY LastName
</CFQUERY>
This will retrieve all employees and their extension numbers.
-
To keep our output looking neat and tidy, we will put all the output into an HTML table. We will create a single row for column headers and a second row to contain the field names we want to output. We will then surround the second row with <CFOUTPUT> tags. Because <CFOUTPUT> tags will surround the second row, we will loop through all query results, in effect creating a new row for each record returned.
-
See if you can figure out the code to output the table on your own. For a bit of an extra challenge, try to use some of the special
variables
that are automatically created with each query.
-
Your code should look similar to the code shown in Listing 3.1.
Listing 3.1 EmployeeList.cfm
<!--- retrieve all employees and extensions --->
<CFQUERY NAME="qStaffList" DATASOURCE="Staff">
SELECT FirstName, LastName, Extension
FROM Employees
ORDER BY LastName
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee List</TITLE>
</HEAD>
<BODY>
<!--- output number of records in query results --->
<CFOUTPUT>
<!--- the 'RecordCount' variable is automatically created --->
<B>#qStaffList.RecordCount#</B> Employees Found
</CFOUTPUT>
<!--- begin the output table --->
<TABLE BORDER="1">
<!--- table header row --->
<TR BGCOLOR="#FFFF80">
<TH>No.</TH>
<TH>Last Name</TH>
<TH>First Name</TH>
<TH>Ext.</TH>
</TR>
<!--- output query information--->
<CFOUTPUT QUERY="qStaffList">
<!--- since this row is inside a cfoutput block,
it loops to create a new row for each record--->
<TR>
<!--- the 'CurrentRow' variable is automatically created --->
<TD>#CurrentRow#</TD>
<TD><B>#LastName#</B></TD>
<TD>#FirstName#</TD>
<TD>#Extension#</TD>
</TR>
</CFOUTPUT>
</TABLE>
<!--- end table output --->
</BODY>
</HTML>
-
Save this file as EmployeeList.cfm in your Examples\Step03 folder.
-
Browse to your page and check your work. Remember that you must go through the server (using http://localhost/...). You cannot just use the browser to open the file from the file system.
You should see something similar to the display in Figure 3.10.
|
