- Setting Up a Data Source with ColdFusion MX
- Retrieving Data
- Example: Creating an Employee List
- About this Article
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Example: 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.1.
Listing 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.
Browse to your page and check your work. 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 6.
)
Figure 6 The EmployeeList.cfm browser display.