O'Reilly logo

Programming ColdFusion MX, 2nd Edition by Rob Brooks-Bilson

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Drilldown Queries

A drilldown query (sometimes referred to as master-detail) is one that starts by retrieving and displaying a relatively broad or general result set. Then, hyperlinks from one or more columns in the result set are used to call another template that performs a query based on URL parameters passed by the hyperlinks. This process is designed to narrow the number of records returned until a desired level of granularity is achieved, hence the name drilldown query.

For drilldown queries, you usually need two templates, but it is possible to use as many as you want to achieve the level of granularity you need. In a two-template drilldown application, the first template queries a data source and displays a summary (usually just a few fields) of every record in the data source meeting the user’s criteria. Hyperlinks from some of the fields in these results pass the primary key values of records to the second template. The second template then performs a query using the primary key value passed in via URL in the WHERE clause of the SELECT statement. The results (usually the full record) are then output to the browser. Example 11-6 demonstrates how a two-template drilldown query works by querying the EmployeeDirectory table and generating an HTML table containing the Name, Title, and Department of each employee.

Example 11-6. Initial screen listing partial information about each record

<!--- Retrieve a list of all employees in the EmployeeDirectory table ---> <cfquery name="GetEmployeeList" ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required