Example 3.1: Creating an Employee List

Team-Fly    

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 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.

  1. 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.

  2. 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.

  3. 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.

  4. 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>

  5. Save this file as EmployeeList.cfm in your Examples\Step03 folder.

  6. 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.

    Figure 3.10. The EmployeeList.cfm browser display.

    graphics/03fig10.gif


    Team-Fly    
    Top
     



    ColdFusion MX. From Static to Dynamic in 10 Steps
    ColdFusion MX: From Static to Dynamic in 10 Steps
    ISBN: 0735712964
    EAN: 2147483647
    Year: 2002
    Pages: 140
    Authors: Barry Moore

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net