Understanding Drill-Down Applications and URL Variables

---

To begin your exploration of URL variables, start with a common set of pages you'll find used all over the Web — the "drill-down" or "summary-detail" application. Take a look at Figures 51-1 and 51-2, which show the two elements of a basic drill-down application.

Figure 51-1: The "summary" page of a sample drill-down application, showing only film titles

Figure 51-2: The "detail" page of a drill-down application, showing details on a single film

The template pictured in Figure 51-1 is designed to show the user only the title of films stored in my database. When users click a title, they see something like Figure 51-2, which shows more complete details about the film they selected.

Again, this display method is used often on the Web in shopping carts, catalogs, discographies, genealogy sites, and so on. ColdFusion makes it easy to construct such an application. As you may have guessed, the sample application is displaying film data that has been stored in a database. Because it generates the summary and detail pages dynamically, any new film titles added to the database automatically appear in the summary and are ready to serve up as detail pages.

Setting up the sample files

To get a closer look at the sample drill-down application, you need to define datasource and copy the relevant files to your Web directory. On the CD-ROM that accompanies this book, you'll find a Microsoft Access database named  ed_wood.mdb. Copy it to your hard drive (the db folder in cfusionmx is a good spot for sample datasources), then access the ColdFusion MX Administrator and set up your database as a new Microsoft Access datasource called ed_wood. Remember that if you're using Windows 98 or ME, the process is slightly different--see Chapter 48 for details.

Also on the CD-ROM are two templates named  film_summary.cfm and  film_detail.cfm. Create a new folder called ed_wood in your Web root (usually cfusion\wwwroot) and copy the two files there.

To test the application, start by accessing the film summary page, the one pictured in Figure 51-1. Your URL should be similar to this one:

http://localhost:8500/ed_wood/film_summary.cfm

Now place your mouse over any of the film title links and note the URL displayed in your browser's status bar:

http://localhost:8500/ed_wood/film_detail.cfm?id=8

This URL links to  film_detail.cfm, the page pictured in Figure 51-2, but it also passes some additional information. Everything after the .cfm file extension is called a query string. In this case, the query string contains one variable, id, which is set to a value of 8.

Note 

In URLs, query strings are always separated from the name of a page by a question mark. If more than one variable is used in a query string, each variable is delimited by an ampersand (&), for example:

some_page.cfm?firstname=John&lastname=Smith&age=21

Query strings can't contain spaces, control characters, or punctuation marks, other than those used to identify variables and a handful of special characters such as a dash (-). Many Web technologies other than ColdFusion, including CGI scripts, Active Server Pages, PHP3 pages, and so on, use query strings.

Variables passed in a query string are called URL variables. These variables are available for use in the called page (in this case,  film_detail.cfm) like variables that have been passed by a form, or those that result from a <cfquery>. As you learn in the next section, when you refer to a URL variable in a ColdFusion template you enclose it in hash marks as you would any other ColdFusion variable. It's also good practice to add a scope to the variable, using the prefix URL, such as #URL.id# or #URL.firstname#.

Examining a detail page

Before you look at the code that produces the summary page, work backwards and first take a look at code that produced the detail page shown in Figure 51-2. You can open  film_detail.cfm in Dreamweaver, or view its source in Listing 51-1.

Listing 51-1: film_detail.cfm

<!---query the database for a specific film, based on the ID sent in  the URL--->     <cfquery name="get_film" datasource="ed_wood">      select *       from films       where id = #URL.id# </cfquery>     <html> <head>     <!---display the film's title in the browser title bar--->     <title>      Details on <cfoutput>#get_film.title#</cfoutput> </title>     <meta http-equiv="Content-Type" content="text/html; charset=iso- 8859-1"> </head>     <body>     <!---display the details on this film--->     <cfoutput query="get_film">      <h3>#title#</h3>      <p><b>Year of release:</b> #year#</p>      <p><b>Description:</b> #description#</p> </cfoutput>     </body> </html>

Note the <cfquery> section in the file. It's designed to retrieve only one record: the one in which the id is equal to the ID value passed in the query string. In the page displayed in Figure 51-2, the query string specifies id=8, so the display page shows the details for the film Bride Of The Monster. To see why, take a look at your copy of the  ed_wood.mdb database, or Figure 51-3, which shows the films table in Datasheet view.

Figure 51-3: The "films" table in the ed_wood datasource

When I created this table in Microsoft Access, I allowed Access to automatically create an ID field. This gives each film a unique numeric identifier and makes it easy to query for a specific record.

What happens if you specify a different ID value in the URL? Try it and see. Manually edit the URL in your browser to look like this one:

http://localhost:8500/ed_wood/film_detail.cfm?id=14

You see the detail page, but this time populated with information on the film with the ID of 14, as shown in Figure 51-4.

Figure 51-4: The template film_detail.cfm, when called with an ID of 14

When the value of the URL variable id changes, so does the content of the page.

Caution 

When you use ID values as the identifiers for a drill-down application, there's a danger that a tech-minded user could "run" your database by creating an automated script that would fetch your pages in numeric order, as follows:

http://some_site/show_member.cfm?member_id=1 http://some_site/show_member.cfm?member_id=2 http://some_site/show_member.cfm?member_id=3

This would allow the user's script to fetch a detail page for each record in your database sequentially and save it for the user's own purposes. For a database of Ed Wood films, this isn't a problem, but it would be if your database contained proprietary information on your site's users. To guard against this, one quick solution is to use non-sequential, random ID values. Microsoft Access, for example, enables you to specify that your id field be made up of randomly generated, nine-digit numbers that may be either positive or negative. This makes it much more difficult for users to run your database, because they would have to write a program to fetch every ID between -999999999 and 999999999, one page at a time.

Examining the summary page

Now take a closer look at the summary page that displays film titles,  film_summary.cfm (see Figure 51-1). In addition to simply retrieving and displaying the film titles stored in the database, the summary page also creates links to pass the correct ID to the detail page. Take a look at the source code in your copy of the file, or in Listing 51-2.

Listing 51-2: film_summary.cfm

 <!---get the id and title for all films in the database--->     <cfquery name="get_films" datasource="ed_wood">      select id, title       from films </cfquery>     <html> <head> <title>The Films of Ed Wood</title> <meta http-equiv="Content-Type" content="text/html; charset=iso- 8859-1"> </head>     <body> <h3>The Films of Ed Wood</h3>     <!---display the query results as an unordered list, wrapping each  title in an <a href> tag pair to create a link to the detail page. each  link will pass the film's database ID in its URL--->     <ul> <cfoutput query="get_films">      <li><a href="film_detail.cfm?id=#id#">#title#</a></li> </cfoutput> </ul>     </body> </html>

The query section of the template should be familiar by now. It simply gets the title and ID for each film record in the database. Further down the page, the <cfoutput> section uses that data to display the title and link for each film:

<a href="film_detail.cfm?id=#id#">#title#</a>

This URL links to the detail page and uses a query string to pass an ID corresponding to the film's title. Recall from the discussion in the previous section that the detail page uses the passed ID to determine which film to display.

Note 

Why use ID values as the identifier for a record? Why not use something more intuitive like the film's title? The answer is that while you could use film titles as identifiers, it would create some problems. For example, the URL used to access the detail page would look like this:

http://localhost:8500/ed_wood/film_detail.cfm?title=Plan 9 From Outer  Space

Because query strings can't include spaces, this would cause an error in the user's browser. There are ways around this problem — notably the URLEncodedFormat() function--but in general it's much more foolproof to use a numeric field such as an ID.

Using ordering and groups to sort query output

When the <cfquery> in Listing 51-2 retrieves film titles, it fetches the records in the order it finds them in the database. If you wanted to display the film titles in alphabetic order, you could use SQL's ORDER BY clause to sort the query results by title, as covered in Chapter 48.

But what if you wanted to display the films grouped year of release? Take a look at Figure 51-5.

Figure 51-5: Grouping query results by year

The list is grouped so that films with a common field — year, in this case — are shown together. Creating a page like this requires two modifications to the original summary page:

• Your query must be sorted on the field on which you want to group your output.

• You must use the "group" attribute in your opening <cfoutput> tag to specify the field on which to group.

Take a look at the following source code (you'll also find this file on your CD as film_ summary_by_year.cfm).

<!---get the id and title for all films in the database. order them by  the "year" field to allow for grouping--->     <cfquery name="get_films" datasource="ed_wood">      select id, title, year       from films      order by year </cfquery>     <html> <head> <title>The Films of Ed Wood</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head>     <body> <h3>The Films of Ed Wood</h3>     <!---display the query results grouped by year--->     <cfoutput query="get_films" group="year">      <b>#year#</b>      <ul>      <!---display the sub-elements of the "year" group--->      <cfoutput>           <li><a href="film_detail.cfm?id=#id#">#title#</a></li>      </cfoutput>      </ul> </cfoutput>     </body> </html>

Note that the <cfquery> section is similar to the one in original summary page, except that it includes "year" as one of the fields to retrieve, and it uses ORDER BY to sort the query results by year.

The <cfoutput> section is also modified. It includes group="year" to specify a grouping, and it also includes an extra set of <cfoutput> tags within the main <cfoutput> section. When you use the "group" attribute, ColdFusion ignores duplicate items in the grouped field, meaning that the main <cfoutput> section displays its contents only once for each unique year it finds. To display subelements of a group (the films released in a given year), you use a second set of <cfoutput> tags.

To better understand how the two <cfoutput> sections work together, open the source code for  film_summary_by_year.cfm, experiment by placing some text within the outer set of <cfoutput> tags, and then preview the page. Next try placing something within the inner set and notice how that affects output.

You can group on any field, as long as your <cfquery> orders your query results by that field. In the films database, however, "year" is the only field that would provide a meaningful group, because none of the other fields contain any duplicate entries.

Note 

The "group" attribute used with <cfoutput> is not related to the GROUP BY clause used in SQL statements. Don't confuse the two.

---