Displaying Database Query Results

Listings 10.2 and 10.3 displayed data in simple line-by-line outputs. But that's not all you can do with ColdFusionin fact, there is no type of output that can't be generated with it. ColdFusion has absolutely nothing to do with formatting and generating output; as long as you can write what you want (in HTML, JavaScript, Flash, DHTML, or any other client technology), ColdFusion generates the output dynamically.

To better understand this, let's look at some alternative output options.

Displaying Data Using Lists

HTML features support for two list typesordered lists (in which each list item is automatically numbered) and unordered lists (in which list items are preceded by bullets). Creating HTML lists is very simple:

Start the list with <ul> (for an unordered list) or <ol> (for an ordered list).
End the list with a matching end tag (</ul> or </ol>).
Between the list's start and end tags, specify the list members (called list items) between <li> and </li> tags.

For example, the following is a simple bulleted (unordered) list containing three names:

 <ul>  <li>Ben Forta</li>  <li>Nate Weiss</li>  <li>Ray Camden</li> </UL>

The numbered (ordered) equivalent of this list would be:

 <ol>  <li>Ben Forta</li>  <li>Nate Weiss</li>  <li>Ray Camden</li> </ol>

So, how would you display the movie list in an unordered list? Listing 10.4 contains the code, which you should save as movies3.cfm. Execute the code in your browser (or in Dreamweaver, if you prefer); the output should look like Figure 10.5.

Listing 10.4. `movies3.cfm`The Movie List in an Unordered List

 <!--- Name:        movies3.cfm Author:      Ben Forta (ben@forta.com) Description: Data-driven HTML list 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 ---> <ul>  <cfoutput query="movies">   <li><strong>#MovieTitle#</strong> - #PitchText#</li>  </cfoutput> </ul> </body> </html>

Figure 10.5. HTML unordered lists are a simple way to display data-driven output.

Let's review Listing 10.4 together. It should look familiar because it's essentially the same code as Listing 10.3 (movies2.cfm), only the actual data output has changed. The new output code is:

 <ul>  <cfoutput query="movies">   <li><strong>#MovieTitle#</strong> - #PitchText#</li>  </cfoutput> </ul>

As you can see, the list is started before the <cfoutput> tag, and it's ended after the </cfoutput> tag. This is importanteverything within the output block is repeated once for every row retrieved. Therefore, if the list was started inside the output block, 23 lists would be generated, with each containing a single movie, instead of a single list containing 23 movies. Only the data to be repeated should be placed inside the output block.

The output code itself is simple. For the first row, the code

 <li><strong>#MovieTitle#</strong> - #PitchText#</li>

becomes

 <li><strong>Being Unbearably Light</strong>  - Love, betrayal, and battling eating disorders</li>

which is a valid list item with the movie title in bold (using <strong> and </strong>) is followed by the tag line.

NOTE

As you can see, changing output formatting affects (or should affect) only an isolated portion of your code. As such, many developers first test whether their code works using simple output (line breaks or lists) before they write complex user interfaces. This can make development much easier (debugging core code and the user interface at the same time is no fun).

CAUTION

Be careful when placing code within an output block. Only code that is to be repeated for each row should be placed between <cfoutput> and </cfoutput>. Any other code should go outside the tags.

Displaying Data Using Tables

Probably the layout feature most frequently used (and most useful) is tables. HTML tables enable you to create grids that can contain text, graphics, and more. Tables are used to facilitate a more controlled page layout, including placing content side by side, in columns, and wrapped around images.

Creating tables involves three sets of tags:

<table> and </table> Used to create the table
<tr> and </tr> Used to create rows in the table
<td> and </td> Used to insert cells within a table row (<th> and </th> also can be used for header cellsessentially data cells formatted a little differently, usually centered and in bold)

So, a simple table with a header row, two columns, and three rows of data (as seen in Figure 10.6) might look like this:

 <table>  <tr>   <th>First Name</th>   <th>Last Name</th>  </tr>  <tr>   <td>Ben</td>   <td>Forta</td>  </tr>  <tr>   <td>Nate</td>   <td>Weiss</td>  </tr>  <tr>   <td>Ray</td>   <td>Camden</td>  </tr> </table>

Figure 10.6. HTML tables are constructed using tags to create the table, rows, and individual cells.

TIP

The Dreamweaver Tables toolbar contains buttons and shortcuts to simplify table creation and manipulation.

With that brief intro to HTML tables, let's modify the movie listing to display the list in an HTML table. Listing 10.5 contains a modified version of the code (again, you can use Save As to create a copy of the previous version for editing). Save the file as movies4.cfm, and then execute it to display an output similar to that shown in Figure 10.7.

Listing 10.5. `movies4.cfm`The Movie List in an HTML Table

 <!--- Name:        movies4.cfm Author:      Ben Forta (ben@forta.com) Description: Data-driven HTML table 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 border="1">  <cfoutput query="movies">   <tr>    <td>#MovieTitle#</td>    <td>#PitchText#</td>   </tr>  </cfoutput> </table> </body> </html>

Figure 10.7. Tables provide a convenient mechanism to display data in a grid-like format.

Once again, the code in Listing 10.5 is similar to the previous examples, and once again, it's only the output block that has changed.

The table is created using the code <table border="1">a table with a border. The <table> and </table> tags are placed outside the output block (you want a single table, not a table for each row).

The table needs a new table row for each row in the query. So, the <tr> and </tr> tags are within the output loop, and within them are two cells (containing MovieTitle and PitchText).

As you can see in Figure 10.7, this code creates a single table with as many rows as there are query rows (23 in this example).

TIP

Viewing the source code generated by ColdFusion is useful when 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 doesn't look the way you intended it to look, a good place to start is comparing your template with the source code it generated.

You'll probably find yourself using tables extensively. To ensure that dynamic HTML table creation is properly understood, another example is in order.

This time the table will contain two rows for each query row. The first will contain two cellsone for the title and tag line and one for the release date. The second row will contain the movie summary (and because the summary can be lengthy, its cell spans both columns). The output generated can be seen in Figure 10.8.

Figure 10.8. For greater control, HTML tables can contain cells that span two or more columns (and rows).

Listing 10.6 contains the revised code; this time save the file as movies5.cfm and execute it in your browser.

Listing 10.6. `movies5.cfm`The Movie List in an HTML Table

 <!--- Name:        movies5.cfm Author:      Ben Forta (ben@forta.com) Description: Data-driven HTML table 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">Movie List</font>   </th>  </tr>  <!--- loop through movies --->  <cfoutput query="movies">   <tr bgcolor="##cccccc">    <td>     <strong>#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>

A few changes have been made in Listing 10.6. First, the <cfquery> SELECT statement has been modified to retrieve two additional columnsSummary contains the movie summary, and DateInTheaters contains the movie's public release date.

In addition, the following HTML code has been added before the <cfoutput> tag:

 <tr>   <th colspan="2">    <font size="+2">Movie List</font>   </th>  </tr>

This creates a header cell (header contents usually are centered and displayed in bold) containing the text Movie List as a table title. Because the table is two columns wide, the title must span both columns, so the optional attribute colspan="2" is specified.

The output block itself creates two rows (two sets of <tr> and </tr> tags) per movie. The first contains two cellsone with the MovieTitle and PitchText (with a line break between them) and the other with the release date formatted for display using the DateFormat() function. The second row contains a single cell spanning both columns and displaying Summary.

The DateFormat() function was introduced in Chapter 8.

As seen in Figure 10.8, the table row containing the title and tag line has a colored background. To set the background color of a table row (or a specific table cell, or even the entire table for that matter) the bgcolor attribute is used, and the color is specified using known named (like red and green) or RGB values in hexadecimal notation as follows:

 <tr bgcolor="#cccccc">

Hexadecimal values are preceded by a #, the same character used to delimit ColdFusion expressions. If the above code were used in our <cfoutput> block, ColdFusion would have generated an error message complaining about a missing closing # (it would think that cccccc was an expression needing a closing #). As such, our table code escapes the # as follows:

 <tr bgcolor="##cccccc">

Escaping # was covered in Chapter 8, "Using ColdFusion."

TIP

Pay close attention to which code you place within and without the <cfoutput> block. Misplacing a <tr> or </td> tag could result in a badly formatted HTML table, and some browsers might opt to not even display that table.

As you can see, as long as you know the basic HTML syntax and know what needs to be repeated for each database row and what doesn't, creating dynamic data-driven output is quick and painless.

TIP

ColdFusion features a tag named <cftable> that can be used to automate the entire process of creating data-driven HTML tables. Although this tag works, I recommend against using it. HTML tables aren't difficult to learn and create, and doing so is well worth the effort because you'll find that you have far more control over the exact format and output.

CAUTION

I know I've said it several times already, but because this is one of the most common beginners' mistakes (and a very aggravating one to debug at that), I'll say it one last time:

When creating dynamic output, pay special attention to what needs to be repeated and what does not. Anything that needs to be displayed once per row (either before or after the row) must go in the output block; anything else must not.

NOTE

HTML tables are a useful way to format data, but a cost is associated with using tables. For a browser to correctly display a table, it can't display any part of that table until it has received the entire table from the Web server. This is because any row, even one near the end of the table, can affect the width of columns and how the table will be formatted. Therefore, if you display data in a table, the user will see no data at all until all the data is present. If you were to use another type of displaya list, for examplethe data would be displayed as it was received. In reality, the page likely will take as long to fully load with or without tables. The disadvantage of using tables is that it takes longer for any data to appear. Actual ColdFusion processing time is identical regardless of whether tables are used, but the user perception could be one of a slower application if you create large HTML tables.

Displaying Data Using Lists

Listing 10.4. movies3.cfmThe Movie List in an Unordered List

Figure 10.5. HTML unordered lists are a simple way to display data-driven output.

Displaying Data Using Tables

Figure 10.6. HTML tables are constructed using tags to create the table, rows, and individual cells.

Listing 10.5. movies4.cfmThe Movie List in an HTML Table

Figure 10.7. Tables provide a convenient mechanism to display data in a grid-like format.

Figure 10.8. For greater control, HTML tables can contain cells that span two or more columns (and rows).

Listing 10.6. movies5.cfmThe Movie List in an HTML Table

Listing 10.4. `movies3.cfm`The Movie List in an Unordered List

Listing 10.5. `movies4.cfm`The Movie List in an HTML Table

Listing 10.6. `movies5.cfm`The Movie List in an HTML Table