CFM Tutorial     

Cold Fusion ch5- Basics Con't...

6.6 Using Drill-Down Application

The nature of the World Wide Web places certain restrictions on data interaction. Every time a web browser makes a request, a connection is made to a web server, and that connection is maintained only for as long as it takes to retrieve the web page. Subsequence requests or selections create yet another connection —again, just for the specific request.

Simple user interfaces that you may take for granted in most commercial software, such scrolling through previous or next records with the cursor keys, become quite complex within the constraints of web pages and in how they interact with web servers.

One elegant and popular form of the web-based data interaction is the "Drill-down" approach. Drill down is designed to break up data so that only what is needed on a single page is displayed. Selecting an item on that page causes details about that item to be displayed. The process is called drilling down because you drill through the data layer-by-layer to find the information you need.

6.6.1 Building Dynamic SQL Statement

Creating a drill-down application in Cold Fusion involves creating multiple templates. For example, one template should list the employees, and a second template should list an employee's details.

First, create the detail template. The SQL query in this template has to select detailed user information for a specific user. Obviously, you don't want to create a template for every employee in your database. Doing so would totally defeat the purpose of using templates in the first place. Rather, the template needs to be passed a parameter, a value that uniquely identifies an employee. Fortunately, when you created an Employee table, you created a column called EmployeeID, which contains a unique id for each employee in the table. The following example demonstrates how to pass parameters. 

<CFQUERY
DATASOURCE="a2Z"
NAME="employee"
>
SELECT LastName, 
FirstName, 
MiddleInit,
Title,
PhoneExtension,
PhoneCellular,
PhonePager,
EMail
FROM Employee
WHERE EmployeeID = #EmployeeID#
</CFQUERY>

<CFOUTPUT QUERY="employee">
<html>
	<head>
		<title>#LastName#, #FirstName# #MiddleInit#</title>
	</head>
	<body>
		<h1>#LastName#, #FirstName#</h1>
		<hr>
		Title: #Title#
		<br>
		Cellular: #PhoneCellular#
		<br>
		Pager: #PhonePager#
		<br>
		E-Mail: #EMail#
	</body>
</html>
</CFOUTPUT>
Before you look at the web page produced by this code, take a look at the SQL statement in this CFQUERY tag. The SQL SELECT statement selects the columns needed and uses a WHERE clause to specify the row to select. The WHERE clause cannot be hard-coded for any particular employee ID and, therefore, uses a passed field, #EmployeeID#. The #EmployeeID field is passed to the template as part of the URL.

If an EmployeeID of 7 is passed with the URL, the WHERE clause WHERE EmployeeID = #EmployeeID# becomes WHERE EmployeeID = 7—exactly what you need to select the correct row. Parameters are passed to URLs after the template name, and each parameter is separated by an ampersand character. So, to specify employee ID 7, you add ?EmployeeID=7 to the URL.

6.6.2 Using Frames to Implement Data Drill Down

Sometimes you want to display an employee's details on the same window as the list of employee names. Frames enables you to do that. Using frames, you can split your browser window in two or more windows and control what gets displayed with each. Cold Fusion templates are well suited for use within frames.

Creating frames involves creating multiple templates (or HTML pages). Each window in a frame typically displays a different template. If you want two windows, you need two templates. If addition, you always need to one page that is used to lay out and crate the frames.

When you create frames, each window is named with a unique name. In a non-frame window, every time you select a hyperlink, the new page is opened in the same window, replacing whatever contents were there previously. In a framed window, you can use the window name to control the destination for any output.

6.6.3 Creating Frames for Use with Cold Fusion

Now that you have an idea how frames work, the first thing you need to do is create the template to define and create the frames. The code for template EMPLFRAM.CFM is shown Listing 6.6.2_1.
Listing 6.6.2_1: EMPLFRAM.CFM - Employee Frame Definition and Creation 
<HTML>
<HEAD>
	<TITLE>Employee</TITLE>
</HEAD>
<FRAMESET COLs="50%, 50%">
	<FRAME SRC="employ3.cfm" NAME="employees">
	<FRAME SRC="empdtl1.cfm?EmployeeID=0" NAME="details">
</FRAMESET>
</HTML>
This template first defines the frames. <FRAMESET COL="50%,50%"> creates two columns (windows), each taking up 50% of the width of the browser window.

Then the two columns are defined. The line <FRAME SRC="employ3.cf" NAME="employee"> create the left frame. The NAME attribute names the window, and the SRC attribute specifies the name of the template to initially display within the window when the frame is first displayed.

When the frame is first displayed, no employee is selected yet. Therefore, no information is available for display in the detail window, the right frame. The simplest way to display an empty frame is to specify an nonexistent employeeID in the URL.

The next thing is to create the employee list template. The URL to display the employee details must include a TARGET attribute to designate in which window to display the URL. If the TARGET is omitted, the new data is displayed in the frame from which it is selected. 


<CFQUERY
  DATASOURCE = "a2z"
  NAME = "employees"
>
SELECT FirstName, LastName, PhoneExtension, EmployeeID
FROM Employee
ORDER BY LastName, FirstName
</CFQUERY>
<HTML>
	<HEAD>
		<TITLE>Employee List</TITLE>
	</HEAD>
	<BODY>
	<H1>Employees</H1>
	<UL>
		<CFOUTPUT QUERY="employees">
		<LI><A HREF="empdtl1.cfm?EmployeeID=#EmployeeID#" 
			TARGET="details">#LastName#, #FirstName#</A> - Ext. #PhoneExtension#</LI>
		</CFOUTPUT>
	</UL>
	</BODY>
</HTML>

    References

    (1) the Cold Fusion Web Application Construction Kit, Second Edition.
KHMERCyber.com ©2008