|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Displaying the Query Result Set
|
|
|
|
Once you've created a CFQUERY in your application page file, you can then reference its results within other CFML tags. The query results can be used to dynamically create an HTML page.
As you learn to use CFML tags in application page files, keep in mind that you can also use HTML tags and text in application page files. Wherever you use standard HTML tags and text inside your application page, ColdFusion simply passes the tags and text directly back to the client browser.
The most flexible way to display data retrieved from a CFQUERY is to define a CFML output section in your application page file using the CFOUTPUT tag. Output is generated for each record in a result set. Output can be linked to a specific query or contain content from multiple queries. A CFOUTPUT tag can contain:
- Literal text
- HTML tags
- References to query columns
- References to dynamic parameters like form fields
- Functions
Basic output code has the following syntax:
<CFOUTPUT QUERY="queryname" MAXROWS=n >
Literal text, HTML tags, and
dynamic field references (e.g., #FullName#)
</CFOUTPUT>
|
|
|
|
Database field names in CFOUTPUT sections
|
|
|
Some databases, such as Microsoft Access, allow field names to contain embedded spaces, as in, "Region Name." ColdFusion does not support references to these fields in CFOUTPUT sections. Aside from maintaining compatibility with ColdFusion, avoiding the use of spaces in table names is a sound practice, since it maximizes the portability of your work across database systems. You can use an underscore as a separator, such as "Region_Name"
Field names must always begin with a letter and contain only alphanumeric characters. You can use the SQL keyword AS to alias field names. For example, the statement
SELECT 401K AS FK FROM Employee_Withholding
will be processed correctly in a CFQUERY.
|
|
|
|
Example: CFOUTPUT
|
|
|
Use the CFOUTPUT tag to display data from the result set of the EmployeeList query in a browser. For this example, we've chosen to display the first name, last name, and phone number of each employee. It's good programming practice to specify the query name as part of the variable name.
The following example shows the complete code for the application page:
<!--- Query to select customers --->
<CFQUERY NAME="EmployeeList" DATASOURCE="CompanyDB">
SELECT *
FROM Employees
</CFQUERY>
<HTML>
<HEAD>
<TITLE>Employee List</TITLE>
</HEAD>
<BODY>
<H2>Employee List</H2>
<!--- Output section --->
<CFOUTPUT QUERY="EmployeeList">
<HR>
#EmployeeList.FirstName# #EmployeeList.LastName#
(Phone: #EmployeeList.PhoneNumber#) <BR>
</CFOUTPUT>
</BODY>
</HTML>
You could call this application page using a standard URL reference:
http://myserver/cfdocs/employeelist.cfm
The application page reference can be in a hyperlink, as well.
<A HREF="myserver/cfdocs/employeelist.cfm">Employee List</A>
The output, formatted using the HTML <HR> and <BR> tags, would look like this:
<HR>
Deborah Jones (Phone: 612-227-1019) <BR>
<HR>
John Smith (Phone: 507-452-7224) <BR>
<HR>
Frank Wilson (Phone: 612-831-9555) <BR>
|
|
|
|
Nested CFOUTPUT and grouping |
|
|
|
You can nest CFOUTPUT tags to create grouped displays of output, much like the grouping features of most database report writers. Grouping is accomplished by adding the GROUP attribute to a CFOUTPUT tag and then nesting another CFOUTPUT tag within the first. The formatting instructions above and below the inner tag display the group header and footer information. The formatting instructions in the inner CFOUTPUT tag display record detail information.
|
|
|
|
Example: Grouping
|
|
|
The following code uses the CFOUTPUT GROUP attribute to display the query results based on the "CourseLevel" value of the query. The ORDER BY keyword of the SQL statement sorts the result set by the CourseLevel field. There is no limit to the number of CFOUTPUT statements that you can nest together. If you want to use multiple levels of grouping, you need to set multiple levels of sorting in your SQL query (e.g., "ORDER BY Region, State").
<CFQUERY NAME="Courses" DATASOURCE="CourseDB">
SELECT *
FROM CourseList
WHERE Department_ID = '#Form.Department#'
ORDER BY CourseLevel
</CFQUERY>
<CFOUTPUT QUERY="Courses" GROUP="CourseLevel">
<H4>#CourseLevel#</H4>
<UL>
<CFOUTPUT>
<LI> #CourseNumber# - #CourseName#
</CFOUTPUT>
</UL>
</CFOUTPUT>
The area between the outer CFOUTPUT containing the GROUP attribute and the inner CFOUTPUT containing the "CourseNumber" and "CourseName" parameters contains the text and formatting for the header of each section. Correspondingly, the area below the inner CFOUTPUT contains the text and formatting for the footer of each section.
The output from this application page is three course level headings (Basic, Intermediate, and Advanced). Each level contains an unordered list of the courses offered at that level.
The final output in a Web browser would look like this:
Basic
100 - Physiology
Intermediate
510 - Neurobiology
500 - Plant Biology
Advanced
820 - Neurobiology
800 - Microbiology
|
|
|
|
CFQUERY properties |
|
|
|
Each CFQUERY you execute has three properties that you can access to provide record number information.
Record Numbers
|
Record Numbers
|
Description
|
RecordCount
|
The total number of records returned by the query.
|
CurrentRow
|
The current row of the query being processed by CFOUTPUT.
|
ColumnList
|
Returns a comma-delimited list of the query columns.
|
|
|
|
|
Example: Query columns
|
|
|
To report the number of records returned from the CustomerList query and a list of the query columns use the syntax:
<CFOUTPUT>
The search returned information
on #CustomerList.RecordCount# customers.<BR>
Columns queried were #CustomerList.ColumnList#.
</CFOUTPUT>
To print a row number next to each record displayed in a query, use the syntax:
<CFOUTPUT QUERY="CustomerList">
#CurrentRow# - #FirstName# #LastName# <BR>
</CFOUTPUT>
|
|
|
|
Returning partial recordsets |
|
|
|
For large recordsets you may want to display only a portion of the records retrieved. You can accomplish this with the STARTROW and MAXROWS attributes of the CFOUTPUT tag.
STARTROW designates the first row of the recordset that should be returned in the output. The MAXROWS determines the number of rows that will be returned in total.
|
|
|
|
Example: Partial recordset
|
|
|
This example shows returning records 10-20 from the recordset created by the EmployeeList query:
<CFOUTPUT QUERY="EmployeeList"
STARTROW="10" MAXROWS="20">
#FirstName# #LastName# #Phone# <BR>
</CFOUTPUT>
|
|
|
|
Using parameters in CFOUTPUT sections |
|
|
|
CFOUTPUT sections are not used exclusively for outputting information returned from queries. You can also use CFOUTPUT sections to display Form, URL, Cookie, Client, Server, Session, Application, and CGI environment parameters. be sure to qualify references to the parameters with the appropriate prefix (Form, URL, or CGI) so that ColdFusion is clear that the parameters are not referring to columns in the query result set. Like column names passed from a query, parameters must be enclosed in pound signs (#). The parameter's value is printed once for every row in the result set. For example, to report the criteria a user entered into the employee search form, use the syntax:
<CFOUTPUT>
<P>The search for #Form.LastName# in the
#Form.Department# returned these results:</P>
</CFOUTPUT>
|
|
|
|
Using the pound sign in CFOUTPUT sections |
|
|
|
Because the # sign serves as a special formatting code for ColdFusion, you need to take special measures when you include it in a CFOUTPUT section. To include a # sign that is not used as a field delimiter, use two consecutive # signs (##).
For example:
<CFOUTPUT QUERY="CustomerList">
Phone ##: #PhoneNumber# <BR>
</CFOUTPUT>
When ColdFusion processes the output, the two pound signs that follow the text "Phone" print as a single pound sign.
|
|
|
  
|
|
|
AllaireDoc@allaire.com
Copyright © 1998, Allaire Corporation. All rights reserved.
|