Programming ColdFusion

Chapter 11: Advanced Database Techniques

This chapter attempts to strengthen the concepts we have already covered while adding several advanced techniques to your bag of ColdFusion tricks. These techniques include advanced ways to display query results, query-caching strategies, and advanced SQL topics. The advanced display techniques we'll cover allow you to enhance the way you display dynamically generated data beyond simple HTML table dumps. Taking advantage of ColdFusion's query-caching abilities allows you to shave precious processing time off your frequently run queries. The advanced SQL topics cover the essentials necessary for building dynamic, highly scalable applications.

Display Techniques

This section focuses on techniques you can use to enhance the display of dynamic data. Some of these techniques include displaying limited record sets, creating dynamic HTML tables with alternating row colors, working with various multicolumn output displays, and browsing records with next/previous. You will also learn several methods for controlling whitespace in dynamic pages in order to optimize page-download times.

Flushing Page Output

A complaint often heard regarding web applications is the amount of time it takes to return data to a user once a page is requested, be it by a form submission or a URL the user clicks on. Often this is due to the large amount of data a particular operation must sift through and return to the user. In situations such as this, it is often desirable to present the user with a "Please Wait" message while their request processes or to provide incremental amounts of data as results from a large query result set become available. ColdFusion lets you handle these tasks with a new tag introduced in Version 5.0 called CFFLUSH. The CFFLUSH tag provides a means to send incremental amounts of data from your ColdFusion server to a user's browser as they become available.

The first time a CFFLUSH tag is encountered on a page, it sends all the HTTP headers for the request along with any generated content up to the position in the template where the tag is encountered. Successive CFFLUSH tags return any content generated since the previous flush. Because of this, CFFLUSH is usually used within loops or output queries to send results back to the browser in incremental chunks. The following example shows the CFFLUSH tag used to incrementally return the results of a database query:

<H1>Outputting Query Results</h2>

Please be patient as this may take a few moments...

<P>

<!--- flush the output up to this point --->

<CFFLUSH>
 

<CFQUERY NAME="GetEmployees" DATASOURCE="ProgrammingCF">

SELECT *

FROM EmployeeDirectory

</CFQUERY>
 

<CFSET Stall=0>

<CFLOOP QUERY="GetEmployees">
 
 <!--- flush the rest of the output as it is generated in chunks of 100 
 
       bytes --->
 
 <CFFLUSH INTERVAL="100">
 
 <!--- use this loop to exaggerate the processing time --->
 
 <CFLOOP INDEX="i" FROM="1" TO="3500">
 
   <CFSET Stall = Stall+1>
 
 </CFLOOP>
  
 
 <CFOUTPUT>
 
 #Name#<BR>
 
 </CFOUTPUT> 

</CFLOOP>

If you run this example, you'll notice all of the content before the first CFFLUSH tag is output almost immediately. After that, the next CFFLUSH tag is used in a loop and specifies that the rest of the content generated by the page should be sent to the browser in chunks of 100 bytes. This is achieved by setting the INTERVAL attribute of CFFLUSH to 100. The code in the example contains an index loop that essentially ties up processing time for 3,500 iterations between the output of each name returned by the query. This results in an artificially inflated amount of time required to output the query results, which is perfect to demonstrate how the CFFLUSH tag incrementally sends data back to the browser in chunks as it becomes available. This is done because the EmployeeDirectory table doesn't contain enough records to effectively demonstrate the CFFLUSH tag.

Once a CFFLUSH tag has been used in a template, you can't use any other CFML tags that write to the HTTP header; doing so causes ColdFusion to throw an error because the header has already been sent to the browser. These tags include CFCONTENT, CFCOOKIE, CFFORM, CFHEADER, CFHTMLHEAD, and CFCONTENT. In addition, attempting to set a variable in the cookie scope with the CFSET tag results in an error. This is because cookies are passed from the server to the browser in the HTTP header.

Displaying Limited Record Sets

You may decide that for a given application, it's more effective not to display the contents of an entire record set. For these applications, you can use two additional optional attributes of the CFOUTPUT tag to display a subset of the full record set returned by a query:

STARTROW

Specifies what query row to begin outputting from

MAXROWS

Specifies the maximum number of rows to output

Example 11-1 uses the STARTROW and MAXROWS attributes of the CFOUTPUT tag to output a subset of a full record set.

<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
 
        SELECT Name, Title, Department, Email, PhoneExt
 
        FROM EmployeeDirectory

</CFQUERY>
 

<!--- display the total number of records returned by the query --->

<H2>Displaying a Limited Record Set</H2>

<CFOUTPUT>

<H3>#GetEmployeeInfo.RecordCount# total records - Displaying records 6-10</H3>

</CFOUTPUT>
 

<!--- output rows 6-10 of the query result set --->

<TABLE CELLPADDING="3" CELLSPACING="0">

<TR BGCOLOR="#888888">
 
 <TH>Record Number</TH>
 
 <TH>Name</TH>
 
 <TH>Title</TH>
 
 <TH>Department</TH>
 
 <TH>E-mail</TH>
 
 <TH>Phone Extension</TH>

</TR>    

<CFOUTPUT QUERY="GetEmployeeInfo" STARTROW="6" MAXROWS="5">

<TR BGCOLOR="##C0C0C0">
 
 <TD>#CurrentRow#</TD>
 
 <TD>#Name#</TD>
 
 <TD>#Title#</TD>
 
 <TD>#Department#</TD>
 
 <TD><A HREF="Mailto:#Email#">#Email#</A></TD>
 
 <TD>#PhoneExt#</TD>

</TR>    

</CFOUTPUT>

</TABLE>

In Example 11-1, a query is performed against the EmployeeDirectory table. Setting the STARTROW attribute to 6 and the MAXROWS attribute to 5 results in records 6 to 10 of the query result set getting output to the browser.

Alternating Row Color in HTML Tables

Another popular way to display tabular data is to alternate the background color of the rows being displayed. The technique is easy to implement and offers an attractive way to display tabular data, so that it stands out. Example 11-2 generates an HTML table of alternating row color from a database query. The results can be seen in Figure 11-1.

<CFQUERY NAME="GetEmployeeInfo" DATASOURCE="ProgrammingCF">
 
        SELECT Name, Title, Department, Email, PhoneExt
 
        FROM EmployeeDirectory

</CFQUERY>
 

<TABLE CELLPADDING="3" CELLSPACING="0">

<TR BGCOLOR="#888888">
 
  <TH>Name</TH>
 
  <TH>Title</TH>
 
  <TH>Department</TH>
 
  <TH>E-mail</TH>
 
  <TH>Phone Extension</TH>

</TR>    
 

<CFOUTPUT QUERY="GetEmployeeInfo">

<TR BGCOLOR="###IIF(GetEmployeeInfo.currentrow MOD 2, DE('E6E6E6'), 

DE('C0C0C0'))#">
 
  <TD>#Name#</TD>
 
  <TD>#Title#</TD>
 
  <TD>#Department#</TD>
 
  <TD><A HREF="Mailto:#Email#">#Email#</A></TD>
 
  <TD>#PhoneExt#</TD>

</TR>    

</CFOUTPUT>

</TABLE>

We alternate the row color by using the IIF( ) and DE( ) functions along with the MOD operator to determine whether or not the row number for the current record is odd or even. Depending on the outcome of the evaluation, one color or the other is used as the background color for the current row. Because hex color codes are supposed to begin with a pound sign (#), we have to create an escape sequence before we call the IIF( ) function. This is done by doubling up on the first pound sign.

Multicolumn Output

Another popular formatting technique involves outputting a query result set in more than one column, similar to how a newspaper story is printed. There are several techniques you can use to achieve multicolumn output. Two of the more popular methods are covered in the following sections.

Sorting multicolumn output from left to right

One technique for outputting a result set in more than one column involves sorting the results from left to right, then top to bottom. You can see the technique for sorting multicolumn output from left to right in Example 11-3....