Using Query Variables


So far, you have displayed data retrieved using database queries. But sometimes you'll need access to data about queries (and not just data within queries). For example, if you wanted to display the number of movies retrieved, where would you get that count from?

To simplify this type of operation, ColdFusion includes special variables in every query. Table 10.1 lists these variables, and as you can see, RecordCount can provide the number of rows retrieved.

Table 10.1. Query Variables

VARIABLE

DESCRIPTION

ColumnList

Names of columns in query results (comma-delimited list)

ExecutionTime

Query execution time (in milliseconds), access as CFQUERY.ExecutionTime

RecordCount

Number of rows in a query


To demonstrate using these special variables, create the file movies6.cfm, as shown in Listing 10.7. This code, which is based on movies5.cfm, generates the output seen in Figure 10.9. Save the code, and execute it in your browser.

Listing 10.7. movies6.cfmUsing Query Variables
 <!--- Name:        movies6.cfm Author:      Ben Forta (ben@forta.com) Description: Using query variables Created:     12/15/04 ---> <!--- Get movie list from database ---> <cfquery name="movies" datasource="ows"> SELECT MovieTitle, PitchText,        Summary, DateInTheaters FROM Films  ORDER BY MovieTitle </cfquery> <!--- Create HTML page ---> <html> <head>  <title>Orange Whip Studios - Movie List</title> </head> <body> <!--- Start table ---> <table>  <tr>   <th colspan="2">    <font size="+2">    <cfoutput>    Movie List (#Movies.RecordCount# movies)    </cfoutput>    </font>   </th>  </tr>  <!--- loop through movies --->  <cfoutput query="movies">   <tr bgcolor="##cccccc">    <td>     <strong>#CurrentRow#: #MovieTitle#</strong>     <br>     #PitchText#    </td>    <td>     #DateFormat(DateInTheaters)#    </td>   </tr>   <tr>    <td colspan="2">     <font size="-2">#Summary#</font>    </td>   </tr>  </cfoutput>  <!--- End of movie loop ---> </table> </body> </html> 

Figure 10.9. RecordCount can be accessed to obtain the number of rows in a query.


So, what changed here? Only two modifications were made to this code. The title (above the output block) now reads as follows:

 Movie List (#Movies.RecordCount# movies) 

#Movies.RecordCount# returns the number of rows retrievedin this case, 23. Like any other expression, the text Movies.RecordCount must be enclosed within number signs and must be between <cfoutput> and </cfoutput> tags. But unlike many other expressions, here the prefix Movies is required. Why? Because this code isn't within a query-driven <cfoutput> (there is no query attribute). Therefore, for ColdFusion to know which query's count you want, you must specify it.

TIP

Here the query name prefix is required because the query was not specified in the <cfoutput> loop. Within an output loop, the query name isn't required, but it can be used to prevent ambiguity (for example, if there were variables with the same names as table columns).


Here you use RecordCount purely for display purposes. But as you will see later in this chapter, it can be used in other ways, too (for example, checking to see whether a query returned any data at all).

Incidentally, why is Movies.RecordCount not in a <cfoutput query="Movies"> block? I'll not answer that one because the last time I explained it, I said it would be the last time I would do so. (That was your hint.)

The other line of code that changed is the movie title display, which now has #CurrentRow#: in front of it. CurrentRow is another special variable, but this time it's in <cfoutput> instead of <cfquery>. Within an output loop, CurrentRow keeps a tally of the iterationsit contains 1 when the first row is processed, 2 when the second row is processed, and so on. In this example, it's used to number the movies (as seen in Figure 10.9).

CurrentRow can also be used it to implement fancy formatting, for example, alternating the background color for every other row (a green paper effect) as seen in Figure 10.10. Listing 10.8 is movies7.cfm, a modified version of movies4.cfm (I used that older version as it's simpler and looks better for this example). Background color, as previously seen, is set using the bgcolor attribute, but unlike in the previous example, here the colors are being set dynamically and programmatically.

Figure 10.10. RecordCount can be used to alternate output colors.


The big change in Listing 10.8 is the <cfif> statement right inside the <cfoutput> loop. As you will recall, <cfif> is used to evaluate if statements (conditions), and here the following <cfif> statement is used:

 <cfif CurrentRow MOD 2 IS 1> xref 

<cfif> was introduced back in Chapter 9, "CFML Basics".
Listing 10.8. movies7.cfmImplementing Alternating Colors
 <!--- Name:        movies7.cfm Author:      Ben Forta (ben@forta.com) Description: Implementing alternating colors Created:     12/15/04 ---> <!--- Get movie list from database ---> <cfquery name="movies" datasource="ows"> SELECT MovieTitle, PitchText FROM Films  ORDER BY MovieTitle </cfquery> <!--- Create HTML page ---> <html> <head>  <title>Orange Whip Studios - Movie List</title> </head> <body> <h1>Movie List</h1> <!--- Display movie list ---> <table>  <cfoutput query="movies">   <!--- What color should this row be? --->    <cfif CurrentRow MOD 2 IS 1>     <cfset bgcolor="MediumSeaGreen">    <cfelse>     <cfset bgcolor="White">    </cfif>   <tr bgcolor="#bgcolor#">    <td>#MovieTitle#</td>    <td>#PitchText#</td>   </tr>  </cfoutput> </table> </body> </html> 

CurrentRow contains the current loop counter as previously explained. MOD is an arithmetic operator that returns the reminder of an equation, and so testing for MOD 2 is a way to check for odd or even numbers (divide a number by 2, if the remainder is 1 the number is odd otherwise the number is even). So checking MOD 2 IS 1 is effectively checking that the number is odd.

Within the <cfif> statement one of two <cfset> tags will be called; if the CurrentRow is odd then the first is called (setting a variable named bgcolor to MediumSeaGreen), and if even then the second if called (setting bgcolor to white). Once the </cfif> is reached a variable named bgcolor will exist and will contain a color (MediumSeaGreen or white, depending on whether CurrentRow is odd or even). As the <cfif> code is within the <cfoutput> block it's processed once for every row, and so bgcolor is reset on each row.

See Chapter 8, "Using ColdFusion", for an introduction to the <cfset> tag.


Then bgcolor is then passed to the <tr> tag's bgcolor attribute so that on odd rows the <TR> tag becomes:

 <tr bgcolor="MediumSeaGreen"> 

and on even rows it becomes:

 <tr bgcolor="White"> 

The result is shown in Figure 10.10.

TIP

You'll notice that I named the variable in Listing 10.8 bgcolor, the same as the HTML attribute with which it was used. This isn't required (you may name variables as you wish) but doing so makes the code clearer as the variable's use is then blatantly obvious.


NOTE

The value in CurrentRow isn't the row's unique ID (primary key). In fact, the number has nothing to do with the table data at all. It's merely a loop counter and should never be relied on as anything else.




Macromedia Coldfusion MX 7 Web Application Construction Kit
Macromedia Coldfusion MX 7 Web Application Construction Kit
ISBN: 321223675
EAN: N/A
Year: 2006
Pages: 282

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