The Cold Fusion Web Database Construction Kit
In Chapter 8, "Introduction to SQL," and Chapter 9, "SQL Data Manipulation," you learned the basics of SQL and interaction with SQL data sources. Now you're ready to start writing Cold Fusion applications. A Cold Fusion application is made up of one or more templates. A template is a file that contains HTML code as well as Cold Fusion Markup Language (CFML) code.
In this chapter, you do not learn how Cold Fusion works and what the various components that make up a Cold Fusion application are. That information is covered in detail in Chapter 2, "Introduction to Cold Fusion." In this chapter, you learn how to create a Cold Fusion template to present dynamic data output. You also learn different techniques for displaying and formatting data and how to implement a "drill-down" interface.
In this chapter, I introduce important Cold Fusion fundamentals. I encourage you to try every one of the examples here yourself because the lessons they demonstrate are the basis for everything covered in the remaining chapters.
As you learned in Chapter 2, all Cold Fusion interaction is via templates rather than HTML files. Templates can contain HTML, Cold Fusion tags and functions, or both.
Cold Fusion templates are plain-text files, just like HTML files are. But unlike HTML files, which are sent to the user's browser, templates are first processed by Cold Fusion. This way, you can embed instructions to Cold Fusion within your templates. If, for example, you want to process user-supplied parameters, retrieve data from a database, or conditionally display certain information, you can instruct Cold Fusion to do so.
But instead of just reading about templates, why don't you create one? The first template you will create just says "Hello" to you. Yes, I know that you can create the same response with any HTML file, but along with saying "Hello," this template also identifies your IP address and the browser you're using. You can't do that with plain HTML.
So, create a text file containing the code in Listing 10.1, and save it in your C:\A2Z\SCRIPTS directory as HELLO1.CFM.
<HTML> <HEAD> <TITLE>Hello!</TITLE> </HEAD> <BODY> <CFOUTPUT> Hello,<BR> Your IP address is: <B>#REMOTE_ADDR#</B><BR> Your browser is: <B>#HTTP_USER_AGENT#</B><P> </CFOUTPUT> </BODY> </HTML>
After you create and save the file, load your browser and type http://yourserver.com/ a2z/hello1.cfm in the URL field (replacing yourserver.com with your own server name). Your browser should display a page that looks similar to the one shown in Figure 10.1. Of course, your IP address and browser information will be different.
Figure 10.1 Using Cold Fusion templates, you can display dynamic data in your Web pages.
Now take a look at the code in Listing 10.1. Most of the code should be familiar to you as standard HTML. The tags for head, title, line breaks, and bold text are all the same HTML that you would use in any other Web page. What is not standard HTML is the <CFOUTPUT> tag and fields surrounded by pound signs (the # character).
All Cold Fusion-specific tags begin with CF; CFOUTPUT therefore is a Cold Fusion- specific tag. You use CFOUTPUT (or Cold Fusion Output) to mark a block of code that Cold Fusion should itself process prior to submitting it to the Web server for sending to your browser. When Cold Fusion encounters a <CFOUTPUT> tag, it scans all the text until the next </CFOUTPUT> for Cold Fusion functions or fields delimited by pound signs.
In Listing 10.1, you use two fields, #REMOTE_ADDR# and #HTTP_USER_AGENT#. They are CGI variables that the HTTP server makes available to CGI applications such as Cold Fusion. #REMOTE_ADDR# contains the IP address of your browser, and #HTTP_USER_AGENT# contains the string with which your browser identifies itself. When Cold Fusion encounters the text #REMOTE_ADDR# in the CFOUTPUT block, it replaces the text with the value in the REMOTE_ADDR CGI variable. And when Cold Fusion encounters #HTTP_USER_AGENT# on the next line, it replaces that text with the appropriate CGI variable, too. Instead of sending the text you entered back to your browser, Cold Fusion replaces the file names with the field values and sends those values back to you instead.
See the "Understanding Cold Fusion Fundamentals" section in Chapter 2 for a detailed discussion of CGI applications and variables.
See the CD for "CGI Environment Variables," for a complete list of all CGI variables and descriptions of each.
So why do you need the <CFOUTPUT> block? Well, take a look at what Cold Fusion would have done without it. Listing 10.2 contains a modified version of the code you used earlier; the output appears twice this time, once within a CFOUPUT block and once not.
<HTML> <HEAD> <TITLE>Hello!</TITLE> </HEAD> <BODY> <I>The next 3 lines <B>are not</B> within a CFOUTPUT block.</I><BR> Hello,<BR> Your IP address is: <B>#REMOTE_ADDR#</B><BR> Your browser is: <B>#HTTP_USER_AGENT#</B><P> <CFOUTPUT> <I>The next 3 lines <B>are</B> within a CFOUTPUT block.</I><BR> Hello,<BR> Your IP address is: <B>#REMOTE_ADDR#</B><BR> Your browser is: <B>#HTTP_USER_AGENT#</B><P> </CFOUTPUT> </BODY> </HTML>
Figure 10.2 Fields not contained within a CFOUTPUT block are output as you enter them, not replaced with their values.
As you can see from the browser output in Figure 10.2, if you use fields outside a CFOUTPUT block, Cold Fusion displays the field name as you entered it, complete with the delimiting characters. More often than not, this result is not what you want.
TIP: Every <CFOUTPUT> must have a corresponding </CFOUTPUT> tag, and vice versa. If you omit either tag, Cold Fusion returns a syntax error.
In the first example, you used Cold Fusion to display dynamic data by specifying the field names for two CGI variables. You also can use Cold Fusion to display process parameters passed to a URL in exactly the same way.
To pass a parameter to a template, you could specify the parameter name and value within the URL. For example, to pass a parameter NAME with a value of BEN, you add &NAME=BEN to the URL. If you specify multiple URL parameters, then you must separate each one with an ampersand character (the & character).
Try this example yourself. Listing 10.3 contains a template that displays the value of a parameter called NAME, if it exists. To display the value, you use the <CFIF> tag to create a condition and a Cold Fusion function called ParameterExists. If the parameter NAME exists, then its value is displayed; otherwise, you are notified that the parameter is not passed.
See the "Using Conditions" section in Chapter 12, "Using Forms to Add or Change Data," for a complete explanation of the CFIF tag and its usage.
See the "Using Optional Fields" section in Chapter 11, "Cold Fusion Forms," for an explanation of the ParameterExists function.
<HTML> <HEAD> <TITLE>Hello!</TITLE> </HEAD> <BODY> Hello,<BR> <CFIF #ParameterExists(name)# IS "Yes"> <CFOUTPUT> The name you entered is <B>#name#</B> </CFOUTPUT> <CFELSE> You did not pass a parameter called NAME </CFIF> </BODY> </HTML>
After you create and save the file as HELLO3.CFM in the C:\A2Z\SCRIPTS directory, load your browser and type http://yourserver.com/ a2z/hello3.cfm&NAME=BEN (you don't have to use my name, any name will do). Your browser display should look like the one shown in Figure 10.3. Now try the example again, this time without any NAME parameter. You then should see a display like the one shown in Figure 10.4.
Figure 10.3 Cold Fusion converts parameters passed to a URL into fields that you can use within your template.
Figure 10.4 Whenever fields are optional, you should verify that they exist before using them.
So why go to the bother of testing for #ParameterExists(name)#? Well, try removing the <CFIF> statement (you have to remove the <CFELSE> and </CFIF> lines, too) and then type http://yourserver.com/ a2z/hello3.cfm without any NAME parameter. Cold Fusion returns an error message because it has no idea what #name# is. If you instruct Cold Fusion to process a field that does not exist, it complains.
Now that you've seen what Cold Fusion templates look like and know how to create, save, and test them, return to the A2Z Books example.
Your employee database is set up and populated with data, so your next task is to publish this information on your intranet. This way, your users can access an up-to-date employee list at all times without needing any special software to do so. All they need to access the data is a Web browser.
Before you create a Cold Fusion template for your database, first take a look at how not to create this page. Listing 10.4 contains the HTML code for the employee list Web page. The HTML code is relatively simple; it contains header information and then a list of employees in an HTML unordered list <UL>.
<HTML> <HEAD> <TITLE>Employee List</TITLE> </HEAD> <BODY> <H1>Employees</H1> <UL> <LI>Black, Kim - Ext. 4565 <LI>Gold, Marcy - Ext. 4912 <LI>Green, Adrienne - Ext. 4546 <LI>Johnson, Dan - Ext. 4824 <LI>Jones, Steven - Ext. 4311 <LI>Smith, Jack - Ext. 4545 <LI>Smith, Jane - Ext. 4876 <LI>Stevens, Adam - Ext. 4878 <LI>White, Jennifer - Ext. 4345 <LI>Wilson, Lynn - Ext. 4464 </UL> </BODY> </HTML>
Figure 10.5 shows the output that this code listing generates.
Why, then, is a static HTML file not the way to create the Web page? Well, what would you do when a new employee is hired or when an employee leaves the company? What would you do if phone extensions change?
You could directly modify the HTML code to reflect these changes, but you already have all this information in a database. Why would you want to have to enter it all again? You would run the risk of making mistakes, misspelling names, getting entries out of order, and possibly even losing names altogether. And as the number of names in the list grows, so will the potential for errors occurring. Plus, during the period between updating the table and updating the Web page, employees will be looking at inaccurate information.
Figure 10.5 You can create the employee Web page as a static HTML file.
An easier and more reliable solution would be to have the Web page display the contents of your Employee table. This way, any table changes are immediately available to all employees. You can build the Web page dynamically based on the contents of the Employee table.
And so you create your first Cold Fusion template. Enter the code as it appears in Listing 10.5, and save it in the C:\A2Z\SCRIPTS as EMPLOY1.CFM. (Don't worry if the Cold Fusion code does not make much sense yet; I explain it in detail later in this chapter.)
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employees"
>
SELECT FirstName, LastName, PhoneExtension
FROM Employees
ORDER BY LastName, FirstName
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee List</TITLE>
</HEAD>
<BODY>
<H1>Employees</H1>
<UL>
<CFOUTPUT QUERY="Employees">
<LI>#LastName#, #FirstName# - Ext. #PhoneExtension#
</CFOUTPUT>
</UL>
</BODY>
</HTML>
Next, load your browser and type http://yourserver.com/ a2z/employ1.cfm in the URL field (again, replace yourserver.com with your own server name). The results are shown in Figure 10.6.
Figure 10.6 Ideally, the employee Web page should be generated dynamically, based on live data.
Now, compare Figure 10.5 and Figure 10.6. Can you see the difference between them? Look carefully.
Give up? Well, the truth is that they are not at all different. The screen shots are identical. If you were to look at the HTML source code that generated Figure 10.6, you would see that aside from lots of extra white space, the dynamically generated code is exactly the same as the static code you entered in Listing 10.4, and nothing like the dynamic code you entered in Listing 10.5.
So, how does the code in Listing 10.5 become the HTML source code that generates Figure 10.6? In the following sections, I help you review the code listing carefully.
The first lines in Listing 10.5 are a Cold Fusion tag called CFQUERY. CFQUERY (or Cold Fusion Query) is the tag you use to submit any SQL statement to an ODBC data source. The SQL statement is usually an SQL SELECT statement but also can be INSERT, UPDATE, DELETE, or any other SQL statement.
See the "Creating an ODBC Data Source" section in Chapter 9 for a more detailed discussion of ODBC data sources.
See the "Creating Queries" section in Chapter 9 for a detailed discussion of SQL statements and specifically the SQL SELECT statement.
See the "CFQUERY" section of Appendix A, "Cold Fusion Reference," for a detailed discussion of the CFQUERY tag, with examples showing the use of all attributes, because this chapter teaches how to use the CFQUERY tag but does not explain every attribute and feature.
The CFQUERY tag has several attributes, or parameters, that are passed to it when used. The CFQUERY in Listing 10.5 uses the following attributes:
Any text that appears between the <CFQUERY> and </CFQUERY> tags is the SQL code that will be passed to the ODBC driver for processing.
NOTE: The CFQUERY name passed to the NAME attribute must be unique in each Cold Fusion template. If you try to reuse a query name, Cold Fusion returns an error message.
The query NAME you specify is Employees. You will use this name later when you process the results generated by the query.
For the DATASOURCE attribute, you specify A2Z, the name of the data source you created in Chapter 9.
The SQL code we specified was:
SELECT FirstName, LastName, PhoneExtension FROM Employees ORDER BY LastName, FirstName
This statement selects the columns you need from the Employee table and sorts them by last name plus first name. The SQL statement, like all other passed values, is enclosed within quotation marks.
TIP: The SQL statement in Listing 10.5 is broken up over many lines to make the code more readable. Although you can write a long SQL statement that is wider than the width of your browser window, generally you should break up these statements over as many lines as you need.
When Cold Fusion processes the template, the first item it finds is the Cold Fusion tag CFQUERY. Cold Fusion knows which tags it itself must process and which it must pass to the server directly. CFQUERY is a Cold Fusion tag and therefore must be processed by Cold Fusion.
When Cold Fusion encounters a CFQUERY tag, it creates an ODBC request and submits it to the specified data source. The results, if any, are stored in a temporary buffer and are identified by the name specified in the NAME attribute. This process happens before Cold Fusion processes the next line in the template.
The CFQUERY code, and indeed all Cold Fusion markup code, never gets sent on to the server for transmission to the browser. Unlike HTML tags that are browser instructions, CFML tags are instructions to Cold Fusion.
The next lines in the template are standard HTML tags: headers, title, and headings. Because they are not Cold Fusion tags, they are sent to the Web server and then to the client browser.
Next, in Listing 10.5, you create an HTML unordered list using the <UL> tag. The list is terminated a few lines later with a </UL> tag.
The list of employees itself goes between the <UL> and </UL> tags. Each name is a separate list item and therefore begins with an HTML <LI> tag. But instead of listing the employees as shown in Figure 10.5, you use a CFOUTPUT tag.
CFOUTPUT is the same Cold Fusion output tag you used earlier. But this time you use it to create a code block that outputs the results of a CFQUERY. For Cold Fusion to know which query results to output, the query name is passed to CFOUTPUT in the QUERY attribute. The name you provide is the same name assigned to the NAME attribute of the CFQUERY tag. In this case, the NAME is Employees.
The code between the <CFOUTPUT QUERY="Employees"> and </CFOUTPUT> is the output code block. Cold Fusion uses this code once for every row that is retrieved. As 10 rows currently appear in the Employee table, the CFOUPUT code is looped through 10 times. And any HTML or CFML tags within that block are repeated as well, once for each row.
See the "CFOUTPUT" section of Appendix A for a detailed discussion of the CFOUTPUT tag, with examples showing the use of all attributes, because this chapter explains how to use the CFOUTPUT tag and introduces only the features needed for the examples presented here.
As I explained earlier, Cold Fusion uses # to delimit fields. In addition to CGI variables and URL parameters, which you used at the beginning of this chapter, Cold Fusion fields can also be columns retrieved by a CFQUERY. Whatever field you use, Cold Fusion replaces the field name with the actual value. So when Cold Fusion processes the output block, it replaces #LastName# with the contents of the LastName column retrieved in the Employee query. Each time the output code block is used, that row's LastName value is inserted into the HTML code.
Cold Fusion fields can be treated as any other text in an HTML document. You can apply any of the HTML formatting tags to them. In the example, the query results need to be displayed in an unordered list. Each employee's name and phone extension is a list item and, therefore, is preceded by the <LI> tag. As the <LI> tag is included within the CFOUTPUT block, Cold Fusion outputs it along with every row.
So, for employee Kim Black at extension 4565, the line
<LI> #LastName#, #FirstName# - Ext. #PhoneExtension#
becomes
<LI> Black, Kim - Ext. 4565
Only the <LI> tag is within the CFOUPUT block, and not the <UL> and </UL>, because you want only one list, not many. If the <UL> and </UL> are within the CFOUPUT block, you have a new list created for each employee--definitely not the desired result at all.
Figure 10.6 shows the browser display that this template creates. It is exactly the same result as Figure 10.5, but without any new data entry whatsoever.
Welcome to Cold Fusion and the wonderful world of dynamic data-driven Web pages!
The nature of the World Wide Web places certain restrictions on data interaction. Every time a Web browser makes a request, a connection is made to a Web server, and that connection is maintained only for as long as it takes to retrieve the Web page. Subsequent selections and Web requests create yet another connection--again, just for the specific request.
Simple user interfaces that you may take for granted in most commercial software, such as scrolling through previous or next records with the cursor keys, become quite complex within the constraints of Web pages and how they interact with Web servers.
One elegant and popular form of Web-based data interaction is the "drill-down" approach. Drill down is designed to break up data so that only what is needed on a single page is displayed. Selecting an item in that page causes details about that item to be displayed. The processes is called drilling down because you drill through the data layer by layer to find the information you need.
The employee page you just created, for example, displays a simple list of employees and extensions. What if you want to display more information such as title, department, and e-mail address? You can just select more columns in the CFQUERY and display them in the CFOUTPUT code, but doing so would clutter the screen, making it hard to use. A better approach would be to display less information on a page and allow the user to click an employee's name to display more information about that employee. This approach, gradually digging deeper into a data set to find the information you want, is known as drilling down.
Creating a drill-down application in Cold Fusion involves creating multiple templates. For example, one template should list the employees, and a second template should display an employee's details.
First, create the detail template. The SQL query in this template has to select detailed user information for a specific user. Obviously, you don't want to create a template for every employee in your database. Doing so would totally defeat the purpose of using templates in the first place. Rather, the template needs to be passed a parameter, a value that uniquely identifies an employee. Fortunately, when you created the Employee table, you created a column called EmployeeID, which contains a unique employee ID for each employee in the table. The code in Listing 10.6 demonstrates how to pass parameters.
){kind=link}
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employee"
>
SELECT LastName,
FirstName,
MiddleInit,
Title,
PhoneExtension,
PhoneCellular,
PhonePager,
EMail
FROM Employees
WHERE EmployeeID = #EmployeeID#
</CFQUERY>
<CFOUTPUT QUERY="Employee">
<HTML>
<HEAD>
<TITLE>#LastName#, #FirstName# #MiddleInit#</TITLE>
</HEAD>
<BODY>
<H1>#LastName#, #FirstName#</H1>
<HR>
Title: #Title#
<BR>
Extension: #PhoneExtension#
<BR>
Cellular: #PhoneCellular#
<BR>
Pager: #PhonePager#
<BR>
E-Mail: #EMail#
</BODY>
</CFOUTPUT>
Before you look at the Web page produced by this code, take a look at the SQL statement in this CFQUERY tag. The SQL SELECT statement selects the columns needed and uses a WHERE clause to specify which row to select. The WHERE clause cannot be hard-coded for any particular employee ID and therefore uses a passed field, #EmployeeID#. The #EmployeeID# field is passed to the template as part of the URL.
If an EmployeeID of 7 is passed with the URL, therefore, the WHERE clause WHERE EmployeeID = #EmployeeID# becomes WHERE EmployeeID = 7--exactly what you need to select the correct row. As you learned earlier, parameters are passed to URLs after the template name, and each parameter is separated by an ampersand character. So, to specify employee ID 7, you add &EmployeeID=7 to the URL.
Now try this example. Type the URL http://yourserver.com/ a2z/empdtl1.cfm?EmployeeID=7 in the URL field (once again, replace yourserver.com with your own server name) in your browser. The resulting output is shown in Figure 10.7.
Figure 10.7 If you want to create truly dynamic pages, parameters can be passed to Cold Fusion templates and used to create dynamic SQL statements.
To display the details for another employee, you just need to change the value passed to the URL EmployeeID parameter. Try replacing EmployeeID=7 with EmployeeID=5. Changing the parameter displays information on a different employee. You can now use the same template to display details for any employee in the database because the Web page is data driven.
To complete the drill-down application, you need to modify the employee list page to include links to the employee details page. The code for the updated template is shown in Listing 10.7.
){kind=link}
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employees"
>
SELECT FirstName, LastName, PhoneExtension, EmployeeID
FROM Employees
ORDER BY LastName, FirstName
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee List</TITLE>
</HEAD>
<BODY>
<H1>Employees</H1>
<UL>
<CFOUTPUT QUERY="Employees">
<LI><A HREF="empdtl1.cfm?EmployeeID=#EmployeeID#">
#LastName#, #FirstName#</A> - Ext. #PhoneExtension#
</CFOUTPUT>
</UL>
</BODY>
</HTML>
Listing 10.7 is the same as Listing 10.5, with two exceptions. First, you now need the EmployeeID value, so you change the SQL SELECT statement in the CFQUERY to also include this column. Second, you modify the employee's name in the CFOUTPUT code block so that it is a hyperlink to the employee detail page.
The new employee name code reads
<LI> <A HREF="empdtl1.cfm?EmployeeID=#EmployeeID#">#LastName#, #FirstName#</A> - Ext. #PhoneExtension#
When Cold Fusion processes employee 7, this line becomes
<LI> <A HREF="empdtl1.cfm?EmployeeID=7">Black, Kim</A> - Ext. 4565
This way, the URL needed for the hyperlink is dynamic, too. The URL built for each employee also contains the correct employee ID, which can be passed to the employee detail template.
So now try out this example. Go to URL http://yourserver.com/ a2z/employ2.cfm (again, replace yourserver.com with your own server name). Figure 10.8 shows what the output looks like. The only difference between this display and the one in Figure 10.6 is that now the employee names are hyperlinks. You can click any one of these links to display employee details, as shown in Figure 10.9.
Figure 10.8 You can build hyperlink URLs dynamically to create even more dynamic Web pages.
Figure 10.9 By passing parameters to a Cold Fusion template, you can use the same template to display different records without requiring a different HTML page for each.
One problem with the drill-down templates you just created is that every time you view an employee's details you have to click your browser's Back button to return to the employee list page. A more usable approach would be to display the employee list and details at the same time. Fortunately, you can do so easily by using a browser feature called frames. Using frames, you can split your browser window in two or more windows and control what gets displayed within each. And Cold Fusion templates are well suited for use within frames.
Creating frames involves creating multiple templates (or HTML pages). Each window in a frame typically displays a different template. If you have two windows, you need two templates. In addition, you always need one more page that is used to lay out and create the frames.
When you create the frames, each window is named with a unique name. In a nonframed window, every time you select a hyperlink, the new page is opened in the same window, replacing whatever contents were there previously. In a framed window, you can use the window name to control the destination for any output.
So, now that you have an idea how frames work, the first thing you need to do is create the template to define and create the frames. The code for template EMPLFRAM.CFM is shown in Listing 10.8.
<HTML> <HEAD> <TITLE>Employees</TITLE> </HEAD> <FRAMESET COLS="50%,50%"> <FRAME SRC="/cgi/cf.exe?template=employ3.cfm" NAME="employees"> <FRAME SRC="/cgi/cf.exe?template=empdtl1.cfm?EmployeeID=0" NAME="details"> </FRAMESET> </HTML>
This template first defines the frames. <FRAMESET COLS="50%,50%"> creates two columns (or windows), each taking up 50 percent of the width of the browser window.
Then the two columns are defined. The line <FRAME SRC="employ3.cfm"NAME="employees"> creates the left frame. The NAME attribute names the window, and the SRC attribute specifies the name of the template to initially display within the window when the frame is first displayed.
When the frame is first displayed, no employee is selected yet. Therefore, no information is available for display in the details window, the right frame. The simplest way to display an empty frame is to specify an inexistent EmployeeID in the URL. We specified an EmployeeID of 0, and so Cold Fusion finds no rows and does not display anything there at all.
Now, the next thing to do is to create the employee list template. Actually, it is the same as the one in Listing 10.7, with one important difference. The URL to display the employee details must include a TARGET attribute to designate which window to display the URL in. If the TARGET is omitted, the new data is displayed in the frame from which it is selected.
The modified code is shown in Listing 10.9. As you can see, the URL has been modified to include the attribute TARGET="details". This attribute specifies that the new URL should be displayed in the frame named details, the right window.
){kind=link}
){kind=link}
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employees"
>
SELECT FirstName, LastName, PhoneExtension, EmployeeID
FROM Employees
ORDER BY LastName, FirstName
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee List</TITLE>
</HEAD>
<BODY>
<H1>Employees</H1>
<UL>
<CFOUTPUT QUERY="Employees">
<LI><A HREF="empdtl1.cfm?EmployeeID=#EmployeeID#"
TARGET="details">#LastName#, #FirstName#</A> - Ext. #PhoneExtension#
</CFOUTPUT>
</UL>
</BODY>
</HTML>
That's all there is to it. To try out this example, go to URL http://yourserver.com/ a2z/emplfram.cfm. (I am no longer going to remind you to replace yourserver.com with your own server name.) Figure 10.10 shows the output as it appears in framed windows. Try clicking any employee's name in the left window, and the right window then displays employee details.
Figure 10.10 Cold Fusion is well suited for use within HTML frames.
Most Web browsers now support tables. By using the HTML <TABLE> tag, you can display data in a two-dimensional grid. Tables are useful for presenting lists in a clean, columnar display.
Because HTML tables are used so often to display query results in data-driven pages, and the <TABLE> syntax can be confusing at times, the makers of Cold Fusion created a Cold Fusion tag called CFTABLE. The CFTABLE tag is designed to conceal the details involved in creating HTML tables. All you have to do is tell Cold Fusion what data to put in each column, and Cold Fusion generates the <TABLE> markup code for you.
The CFTABLE tag has another important advantage. It enables you to create tables that can be viewed by all browsers, even those that do not support HTML tables. To do this, Cold Fusion renders the output in a nonproportional font and pads fields with spaces so that they line up in columns. Although the resulting table might not look as good as a true HTML table, it is functional and is supported by all browsers.
See the "CFTABLE" section in Appendix A for a detailed discussion of the CFTABLE tag, with examples showing the use of all attributes and how it can be used. This chapter teaches the basic use of the CFTABLE tag and introduces only the features needed for the examples presented here.
For an example of the places where you can use CFTABLE, look at the browser output shown in Figure 10.8. Notice how the phone extension is right next to the name and in a different location on the screen depending on how long the employee's name is. If the employees were listed in a table, the data could be presented in a cleaner and more organized fashion.
Listing 10.10 is based on Listing 10.7, but instead of using an unordered list and presenting each employee as a list item, the list is displayed in a table.
){kind=link}
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employees"
>
SELECT FirstName, LastName, PhoneExtension, EmployeeID
FROM Employees
ORDER BY LastName, FirstName
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee List</TITLE>
</HEAD>
<BODY>
<H1>Employees</H1>
<
CFTABLE
QUERY="Employees"
COLHEADERS
>
<
CFCOL
HEADER="Employee"
TEXT="<A HREF=""empdtl1.cfm?EmployeeID=#EmployeeID#"">#LastName#, #FirstName#</A>"
>
<
CFCOL
HEADER="Extension"
TEXT="Ext. #PhoneExtension#"
>
</CFTABLE>
</BODY>
</HTML>
To create the table, you use the tag <CFTABLE QUERY="Employees" COLHEADERS>. The CFTABLE tag is a special type of CFOUTPUT and, therefore, requires that you specify a QUERY attribute, just like the one you would provide to CFOUTPUT. You use CFTABLE only to display query results, and the QUERY attribute specifies which result set to process.
You use the COLHEADERS attribute to instruct Cold Fusion to create optional column headers for each column in the table.
Next, Cold Fusion needs to know what columns you want to include in your table. You specify each column by using the CFCOL tag. You specify two columns here, one for the employee name and one for the phone extension.
The code for the phone extension column is
<CFCOL HEADER="Extension" TEXT="Ext. #PhoneExtension#">
The HEADER attribute specifies the text to use in the column header. This column has a header with the text Extension in it. The TEXT attribute is required; every CFCOL tag must have one. It tells Cold Fusion what you want to display in this column. The TEXT attribute here contains the expression "Ext. #PhoneExtension#". As Cold Fusion processes each row, it replaces the #PhoneExtension# field with the value of the PhoneExtension column retrieved.
The employee name column may look more complicated, but it really isn't at all. The source for the column is
<CFCOL HEADER="Employee" TEXT="<A HREF=""empdtl1.cfm?EmployeeID=#EmployeeID#"">#LastName#, #FirstName#</A>">
Again, you first specify the text for the optional header in the HEADER attribute. The TEXT attribute contains the text to display, and because the name has to be a hyperlink, you must specify the A HREF link tag, too.
In fact, the contents of the TEXT attribute are almost the same as the hyperlink tag you used in Listing 10.7 earlier--with one notable exception. Notice that the link tag has double quotation marks around the URL instead of the usual single set of quotation marks. You need the double quotation marks to tell Cold Fusion to treat this as a quote, not as the end of the TEXT attribute. If you were to enter a single quotation mark, Cold Fusion would think that the TEXT attribute ends right after the HREF=. And because it would not know what to do with the text after the quotation mark, Cold Fusion would report a syntax error. This process of using double quotes to indicate an actual quote character is called escaping, and the quote character is said to have been escaped.
So, now that you understand the code listing, go ahead and run the template. Go to URL http://yourserver.com/ a2z/employ2.cfm. As you can see in Figure 10.11, the employee names and phone extensions are now displayed in clearly labeled columns.
How is this table created without using the HTML <TABLE> tag? Look at the source code generated by Cold Fusion to find out. Select the View Source option in your browser (in Netscape, choose Document Source from the View menu; in Microsoft Internet Explorer, choose Source from the View menu).
As you can see in Figure 10.12, Cold Fusion uses the HTML <PRE> tag, which displays text exactly as it appears in the source code. Usually, Web browsers ignore white-space characters, such as spaces and line feeds. The <PRE> tag instructs the browser to maintain all spacing and line feeds, allowing Cold Fusion to lay out the data exactly as it wants the browser to display it.
Figure 10.11 Cold Fusion can generate non-HTML tables using the CFTABLE tag.
Figure 10.12 To see how Cold Fusion interprets your template, view the generated markup language code with your browser's View Source option.
TIP: Viewing the source code generated by Cold Fusion is useful in debugging template problems. When you view the source, you are looking at the complete output as it was sent to your browser. If you ever need to ascertain why a Web page does not look like you intended, a good place to start is comparing your template with the source code it generated.
Tables created with the HTML <TABLE> tag, of course, look much better. So Cold Fusion supports HTML tables, too. As you can see in Listing 10.11, to create HTML tables, you just need to specify the HTMLTABLE attribute in the CFTABLE tag.
){kind=link}
){kind=link}
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employees"
>
SELECT FirstName, LastName, PhoneExtension, EmployeeID
FROM Employees
ORDER BY LastName, FirstName
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee List</TITLE>
</HEAD>
<BODY>
<H1>Employees</H1>
<
CFTABLE
QUERY="Employees"
COLHEADERS
HTMLTABLE
>
<
CFCOL
HEADER="Employee"
TEXT="<A HREF=""empdtl1.cfm?EmployeeID=#EmployeeID#"">#LastName#, #FirstName#</A>"
>
<
CFCOL
HEADER="Extension"
TEXT="Ext. #PhoneExtension#"
>
</CFTABLE>
</BODY>
</HTML>
Figure 10.13 shows the same employee list screen rendered in an HTML table. Note that when you're displaying data in an HTML table, standard fonts are used, not the fixed font used when the <PRE> tag is specified. Therefore, you can safely use any other HTML formatting options in the CFCOL TEXT attribute if required. If you want the name in bold, for example, you can specify
TEXT="<A HREF=""empdtl1.cfm?EmployeeID=#EmployeeID#""><B>#LastName#, #FirstName#</B></A>"
And Cold Fusion still can display the table correctly. The <B> and </B> tags are HTML tags, not CFML tags, so Cold Fusion just passes them through to the Web server to be sent to your Web browser.
Figure 10.13 You can use the CFTABLE tag to create HTML tables.
To create this table, Cold Fusion generates HTML table code. This source code, as displayed by the browser's view source function, is shown in Figure 10.14.
Figure 10.14 Cold Fusion can generate all the required code to create HTML tables.
As good as the Cold Fusion <CFTABLE> tag is, it is very limited. HTML tables support many advanced features such as table headers, cells that span multiple rows or columns, borders and border colors, background colors and images, and more. If you really want to control how your tables are displayed, you must resort to creating your tables manually. Listing 10.12 demonstrates how to create a bordered table manually for the employee list.
){kind=link}
){kind=link}
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employees"
>
SELECT FirstName, LastName, PhoneExtension, EmployeeID
FROM Employees
ORDER BY LastName, FirstName
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee List</TITLE>
</HEAD>
<BODY>
<CENTER>
<TABLE BORDER=5>
<TR>
<TH COLSPAN=2>
<H1>Employees</H1>
</TH>
</TR>
<CFOUTPUT QUERY="Employees">
<TR>
<TD>
<A HREF="empdtl1.cfm?EmployeeID=#EmployeeID#">#LastName#, #FirstName#</A>
</TD>
<TD>
Ext. #PhoneExtension#
</TD>
</TR>
</CFOUTPUT>
</TABLE>
</CENTER>
</BODY>
</HTML>
Figure 10.15 shows the output for this listing.
Figure 10.15 Creating tables manually gives you a greater degree of control over table appearance.
Now look at the code in Listing 10.12. First, you create the table with the <TABLE> tag and specify an optional border. HTML tables can have borders of varying thicknesses, and the BORDER attribute specifies the border to use. Then you create a table title and place it in a header cell (specified with the <TH> tag) that spans two columns.
Next comes the CFOUTPUT. As each query row is output, a new table row is created. For this reason, you include a complete table row (the <TR> tag) and cells (the <TD> tag) within the CFOUTPUT code block. And finally, you close the table with a </TABLE> tag.
As you can see, manually creating tables requires more effort and a better understanding of HTML tables, but the rewards are well worth your time.
NOTE: Using HTML tables is a useful way to format data, but a cost is associated with using tables. For a browser to display a table correctly, it cannot display any part of that table until it receives the entire table from the Web server. This happens because any row, even one near the end of the table, can have an effect on the width of columns and how the table is formatted. Therefore, if you display data in a table, the user doesn't see any data at all until all the data is present. If you use another type of display--for example, a list--the data is displayed as it is received. The reality is, the page may likely take as long to fully load with or without tables. The downside of using tables is that it takes longer for any data to appear. This, however, does not apply to tables created without the <TABLE> tag.
Before I introduce a new level of complexity, let me review how Cold Fusion processes queries for you. In Cold Fusion, you create data queries by using the <CFQUERY> tag. CFQUERY performs an SQL operation and retrieves results, if any. Results are stored temporarily by Cold Fusion, and they remain around only for the duration of the processing of the template that contains the query.
To output query results, you use the <CFOUTPUT> tag. CFOUTPUT takes a query name as an attribute and then loops through all the rows that are retrieved by the query. The code block between the <CFOUTPUT> and the </CFOUTPUT> is repeated once for every row retrieved.
All the examples you created to this point displayed results in a single list or a single table. But what do you do if you want to process the results in subsets? For example, suppose you want to list the employees by department. You could just change the SQL statement in the CFQUERY to set the sort order to be department and then, perhaps, by name within each department.
This approach, however, would retrieve the data in the correct order, but how would you display it? If you use CFOUTPUT like you have until now, then every row created by the CFOUTPUT block has to be the same. If one has a department name, then all have to, because every row that is processed is processed with the same block of code. So how do you create the output shown in Figure 10.16?
Figure 10.16 You can use the CFOUTPUT tag to group query results and display them accordingly.
The solution is to group the data results. By grouping, you can have more than one CFOUTPUT loop. To understand how grouping works, look at the template in Listing 10.13.
){kind=link}
){kind=link}
<
CFQUERY
DATASOURCE="A2Z"
NAME="Employees"
>
SELECT DepartmentID, FirstName, LastName, PhoneExtension, EmployeeID
FROM Employees
ORDER BY DepartmentID, LastName, FirstName
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee List</TITLE>
</HEAD>
<BODY>
<H1>Employees</H1>
<CFOUTPUT QUERY="Employees" GROUP="DepartmentID">
<H2>Department #DepartmentID#</H2>
<UL>
<CFOUTPUT>
<LI><A HREF="empdtl1.cfm?EmployeeID=#EmployeeID#">#LastName#, #FirstName#</A> - Ext. #PhoneExtension#
</CFOUTPUT>
</UL>
</CFOUTPUT>
</BODY>
</HTML>
The first changes you make are adding the DepartmentID column to the SQL SELECT statement and modifying the sort sequence with ORDER BY DepartmentID, LastName, FirstName. To group results by a column, that column must be the first in the sort sequence. As you want to sort by DepartmentID, that column is now the first in the ORDER BY list.
The big change, however, is the CFOUTPUT block. You now have two of them, one nested inside the other. The outer CFOUTPUT tag also has a new attribute: GROUP="DepartmentID". A CFOUTPUT tag creates a loop that executes once for each row retrieved by a query. When you add the GROUP attribute, you instruct Cold Fusion to execute the CFOUPUT block only when the group value changes.
If you have seven employees with the exact same DepartmentID, the GROUP CFOUTPUT block is executed just once. In the list, you have 10 employees who work in two departments. The outer CFOUPUT block gets executed twice, once for each department. The first row processed has a DepartmentID of 2, so the CFOUTPUT block is executed. The next six rows processed also have a DepartmentID of 2, so the CFOUTPUT block is not executed for them. The eighth row has a different DepartmentID, with a value of 4, so the CFOUTPUT block is executed. The next two rows also have a DepartmentID of 4, so no CFOUTPUT block is executed for them. That's just the outer CFOUTPUT block. The inner block gets executed for every row, just like the CFOUTPUT blocks you used earlier.
Now look at the output code in Listing 10.13. The outer CFOUTPUT creates a header for each new group and then starts a new unordered list. The inner CFOUTPUT populates that list until the group is completed. Then the outer CFOUTPUT terminates the list, and the process loops to the next DepartmentID. The results are shown in Figure 10.16.
NOTE: Groups may be nested by creating additional CFOUTPUT blocks, one for each group. There is no limit to the number of groups that may be nested as long as these two conditions are met. First, every group must be part of the sort sequence used to retrieve the data. Second, the order that the columns appear in the ORDER BY clause must match the order of the groupings.
Now you can see why the column you want to group on must be the first in the ORDER BY list. For grouping to work, all rows with the same value in the grouping column must be processed as a group. If the group is broken up, as could happen if you do not sort by the grouping column, Cold Fusion executes the outer block at the wrong times, and the resulting groups are fragmented.
You have now used two different types of fields: CGI variables and URL parameters. Cold Fusion supports several field types, as shown in Table 10.1, and fields that are database table columns retrieved with a CFQUERY.
| Field | Description |
| CGI | HTTP CGI variables (see The CD, "CGI Environment Variables") |
| CLIENT | Client variables (see Chapter 23, "Web Application Framework") |
| COOKIE | HTTP client-side cookies (see the CD for "Persistent Client Cookies") |
| FORM | HTML form fields (see Chapter 11, "Cold Fusion Forms") |
| URL | Parameters passed to a URL |
| VARIABLES | Cold Fusion variables (see Chapter 17, "Advanced Cold Fusion Templates") |
In this chapter, you used two of these field types and CFQUERY results. By the time you finish reading this book, you'll be using them all regularly. And sooner or later, you're going to run into a name collision. For example, you may have a form field with the exact same name as a table column or a variable with the same name as a URL parameter. When this situation occurs, how does Cold Fusion know which one to use? Well, the answer is that Cold Fusion doesn't know. You must specify which to use. And the way you specify is by qualifying the field name with the field type.
Listing 10.14 is the same template you created in Listing 10.3, with one difference. The references to field name are fully qualified as URL.name. This way, even if you have any other field called name, Cold Fusion still knows which field you are referring to.
<HTML> <HEAD> <TITLE>Hello!</TITLE> </HEAD> <BODY> Hello,<BR> <CFIF #ParameterExists(URL.name)# IS "Yes"> <CFOUTPUT> The name you entered is <B>#URL.name#</B> </CFOUTPUT> <CFELSE> You did not pass a parameter called NAME </CFIF> </BODY> </HTML>
Use your browser to view this template. The resulting display should be exactly the same as shown in Figure 10.3.
The information in this chapter is very important. I strongly urge you not to go any further until you understand all the examples presented here. Cold Fusion field processing, queries, and output form the basis for almost any application you will develop.
In this chapter, you created your first real Cold Fusion application. I introduced the CFQUERY and CFOUTPUT tags and explained examples of each. You learned how to use Cold Fusion fields and how to pass fields as parameters to a URL. You also learned how to create a drill-down application and how you can use HTML frames to make drill down even more usable. You also learned about the Cold Fusion CFTABLE tag and how to create dynamic tables with and without this tag. You learned how to group output results into organized and logical data sets. And finally, you learned how to specify field types to prevent field name collisions.
For more information about topics mentioned in this chapter, see the following chapters:
© Copyright, Macmillan Computer Publishing. All rights reserved.