In the past few chapters, you created and executed ColdFusion templates. You worked with different variable types, conditional processing, code reuse, and more.
But this chapter is where it starts to get really interesting. Now it's time to learn how to connect to databases to create complete dynamic and data-driven pages.
The examples in this chapter, and indeed all the chapters that follow, use the data in the ows data sources and database. These must be present before continuing.
And I'll remind you just this once, all the files created in this chapter need to go in a directory named 10 under the application root (the ows directory under the Web root).
For your first application, you will create a page that lists all movies in the Films table.
Before you create your first data-driven ColdFusion template, let's look at how not to create this page.
<br> tag). Why is a stati258 file not the way to create the Web page? What would you have to do when a new movie is created, or when a movie is dropped? What would you do if a movie title or tag line changed? You could directly modify th260 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'd run the risk of making mistakesinformation being misspelled, entries out of order, and possibly missing movies altogether. As the number of movies in the list grows, so will the potential for errors. In addition, visitors will be looking at inaccurate information during the period between updating the table and updating the Web page. A much easier and more reliable solution is to have the Web page display the contents of your Films table. This way, any table changes are immediately available to all viewers. The Web page would be dynamically built based on the contents of the Films table. To create your first data-driven ColdFusion template, enter the code as it appears in Listing 10.2 and save it in the 10 directory as movies1.cfm. (Don't worry if the ColdFusion code doesn't make much sense yet; I will explain it in detail in just a moment.) Now, execute this page in your browser as As a reminder, the port number (8500 in the above URL) is only needed if you are using the integrated HTTP server. If you are ColdFusion with an external HTTP server then don't specify the port. The results are shown in Figure 10.2. You could also browse the page right from within Dreamweaver as seen in Figure 10.3. To do this, switch to Design View (click the Show Design View button, or select Design from the View menu) and turn on Live Data View (click the Live Data View button, select Live Data from the View menu, or press Ctrl-Shift-R). Now compare Figure 10.2, you'd see that aside from a lot of extra white space, the dynamically generated code is exactly the same as the static code you entered in Figure 10.1? Let's review the code listing carefully. Chapter 6, "Introducing SQL," for an overview of data sources, SQL and SQL statements. The <cfquery> tag has several attributes, or parameters, that are passed to it when used. The <cfquery> in Listing 10.2 uses only two attributes: name
This attribute is used to name the query and any returned data. datasource
This attribute contains the name of the data source to be used. The query name you specified is movies. This name will be used later when you process the results generated by the query. Don't use reserved words (words that have special meaning to ColdFusion) as your query name. For example, don't name a query URL,as URL is a reserved prefix. Query names passed to <cfquery> need not be unique to each query within your page. If you do reuse query names, subsequent <cfquery> calls will overwrite the results retrieved by the earlier query. You specified ows for the datasource attribute, which is the name of the data source created earlier. datasource is required; without it ColdFusion would not know which database to execute the SQL statement against. The SQL statement to be executed is specified between the <cfquery> and </cfquery> tags. The following SQL statement was used, which retrieves all movie titles sorted alphabetically: ColdFusion doesn't validate the SQL code you specify. If syntax errors exist in the SQL code, ColdFusion won't let you know because that's not its job. The data source will return error messages if appropriate, and ColdFusion will display those to you. But it's the data source (and the database or database driver) that returns those error messages, not ColdFusion. It's important to note that, at this point, no data has been displayed. <cfquery> retrieves data from a database table, but it doesn't display that data. Actually, it does nothing at all with the datathat's your job. All it does is execute a specified SQL statement when the </cfquery> tag is reached. <cfquery> has no impact on generated content at all, and retrieved data is never sent to the client (unless you send it). The next lines in the template are standar259 tags, headers, title, and headings. Because these aren't ColdFusion tags, they are sent to the Web server and then on to the client browser.Listing 10.1. movies275 Code for Movie List
&l275>
<head>
<title>Orange Whip Studios - Movie List</title>
</head>
<body>
<h1>Movie List</h1>
Being Unbearably Light<br>
Charlie's Devils<br>
Closet Encounters of the Odd Kind<br>
Folded Laundry, Concealed Ticket<br>
Forrest Trump<br>
Four Bar-Mitzvahs and a Circumcision<br>
Geriatric Park<br>
Gladly Ate Her<br>
Ground Hog Day<br>
Hannah and Her Blisters<br>
Harry's Pottery<br>
It's a Wonderful Wife<br>
Kramer vs. George<br>
Mission Improbable<br>
Niare on Overwhelmed Street<br>
Raiders of the Lost Aardvark<br>
Silence of the Clams<br>
Starlet Wars<br>
Strangers on a Stain<br>
The Funeral Planner<br>
The Sixth Nonsense<br>
Use Your ColdFusion II<br>
West End Story<br>
</body>
<l>
Listing 10.2. movies1.cfmThe Basic Movie List
<!---
Name: movies1.cfm
Author: Ben Forta (ben@forta.com)
Description: First data-driven Web page
Created: 12/15/04
--->
<!--- Get movie list from database --->
<cfquery name="movies" datasource="ows">
SELECT MovieTitle
FROM Films
ORDER BY MovieTitle
</cfquery>
<!--- Creat260 page --->
&l275>
<head>
<title>Orange Whip Studios - Movie List</title>
</head>
<body>
<h1>Movie List</h1>
<!--- Display movie list --->
<cfoutput query="movies">
#MovieTitle#<br>
</cfoutput>
</body>
<l>
http://localhost:8500/ows/10/movies1.cfm
TIP
Figure 10.2. Ideally, the movie list page should be generated dynamically, based on live data.
TIP
Figure 10.3. ColdFusion pages may be browsed directly within Dreamweaver by switching to Design View with Live Data View enabled.
Understanding Data-Driven Templates
The <cfquery> Tag
CAUTION
NOTE
SELECT MovieTitle
FROM Films
ORDER BY MovieTitle
NOTE
Using <cfoutput> to Display <cfquery> Data
Next, the query results are displayed, one row per line. To loop through the query results, the <cfoutput> tag is used.
<cfoutput> is the same ColdFusion output tag you used earlier (in Chapter 8, "Using ColdFusion"). This time, however, you use it to create a code block that is used to output the results of a <cfquery>. For ColdFusion to know which query results to output, the query name is passed to <cfoutput> in the query attribute. The name provided is the same that was assigned to the <cfquery> tag's name attribute. In this case, the name is movies.
The query name passed to <cfquery> must be a valid (existing) query; otherwise, ColdFusion will generate an error.
The code between <cfoutput query="movies"> and </cfoutput> is the output code block. ColdFusion uses this code once for every row retrieved. Because 23 rows are currently in the Films table, the <cfoutput> code is looped through 23 times. And any HTML or CFML tags within that block are repeated as wellonce for each row.
So what is the minimum number of times a <cfoutput> code block will be processed? It depends on whether you are using the query attribute. Without a query, the code block is processed once. With a query block, it's processed once if a single row exists in the query, and not at all if the query returned no results.
You'll notice that I put the SQL query at the very top of the page instead of right where it was needed (in the middle of the output). This is the recommended way to write your codequeries should be organized at the top of the page, all together. This will help you write cleaner code and will also simplify any testing and debugging if (or rather, when) the need arises.
As explained in Chapter 8, ColdFusion uses # to delimit expressions and variables. ColdFusion expressions also can be columns retrieved by a <cfquery>. Whatever column name is specified is used; ColdFusion replaces the column name with the column's actual value. When ColdFusion processed the output block, it replaced #MovieTitle# with the contents of the MovieTitle column that was retrieved in the movies query. Each time the output code block is used, that row's MovieTitle value is inserted into th260 code.
ColdFusion-generated content can be treated as any other content in a269 document; any of th260 formatting tags can be applied to them. In this example, the query results must be separated by a line break (the <br> tag).
Look at the following line of code:
#MovieTitle#<br>
That first row retrieved is movie Being Unbearably Light, so when processing the first row the above code will generate the following:
Being Unbearably Light<br>
Chapter 25, "Improving Performance," teaches tips and techniques to optimize the performance of data-driven sites.
To see the real power of data-driven pages, take a look at Listing 10.3. This is the same code as in Listing 10.2, but a column has been added to the SQL statement (retrieving PitchText as well now) and the output has been modified so that it displays both the MovieTitle and PitchText columns. Save this file as movies2.cfm (you can edit movies1.cfm and use the Save As option (in the File menu) to save it as movies2.cfm, if you find that easier). Now, execute this page in your browser as follows:
http://localhost:8500/ows/10/movies2.cfm
Again, drop the port if not using the internal HTTP server.
<!--- Name: movies2.cfm Author: Ben Forta (ben@forta.com) Description: Retrieving multiple database columns Created: 12/15/04 ---> <!--- Get movie list from database ---> <cfquery name="movies" datasource="ows"> SELECT MovieTitle, PitchText FROM Films ORDER BY MovieTitle </cfquery> <!--- Creat260 page ---> &l275> <head> <title>Orange Whip Studios - Movie List</title> </head> <body> <h1>Movie List</h1> <!--- Display movie list ---> <cfoutput query="movies"> <strong>#MovieTitle#</strong><br> #PitchText#<p> </cfoutput> </body> <l>
As you can see, two table columns are now used, each delimited by number signs. The MovieTitle is displayed in bold (using <strong> and </strong> tags) and is followed by a line break; on the next line PitchText is displayed followed by a paragraph break. So, for the first row displayed, the previous code becomes
<strong>#MovieTitle#</strong><br> #PitchText#<p>
Compare that to what you'd have had to change in movies to update a static page to look like Figure 10.4, and you'll start to appreciate the dynamic page advantage.
Excited? You should be. Welcome to ColdFusion and the wonderful world of dynamic data-driven Web pages!
![[View full size image]](/image/library/english/13877_10fig01_alt.jpg;437230){kind=link}
![[View full size image]](/image/library/english/13877_10fig02_alt.jpg;437230){kind=link}
![[View full size image]](/image/library/english/13877_10fig03_alt.jpg;437230){kind=link}