Outputting query data



After you define a query, you can use the cfoutput tag with the query attribute to output data from the recordset. When you use the query attribute, keep in mind the following:

  • ColdFusion loops through all the code contained within the cfoutput block, once for each row in the recordset returned from the database.

  • Reference specific column names within the cfoutput block to output the data to the page.

  • You can place text, CFML tags, and HTML tags inside or surrounding the cfoutput block to format the data on the page.

  • Although you do not have to specify the query name when you refer to a query column, use the query name as a prefix for best practices reasons. For example, if you specify the Emplist query in your cfoutput tag, you can refer to the Firstname column in the Emplist query as Firstname. However, using the query name as a prefix, Emplist.Firstname, is preferred, and is in the following procedure.

The cfoutput tag accepts a variety of optional attributes but, ordinarily, you use the query attribute to define the name of an existing query.

  1. Edit emplist.cfm so that it appears as follows:

    <html> 
<head> 
<title>Employee List</title> 
</head> 
<body> 
<h1>Employee List</h1> 
<cfquery name="EmpList" datasource="cfdocexamples"> 
    SELECT FirstName, LastName, Salary, Contract 
    FROM Employee 
</cfquery> 
<cfoutput query="EmpList"> 
#EmpList.FirstName#, #EmpList.LastName#, #EmpList.Salary#, #EmpList.Contract#<br> 
</cfoutput> 
</body> 
</html>

  2. Save the file and view it in your web browser:

    A list of employees appears in the browser, with each line displaying one row of data.

Note: If necessary, refresh your browser to see your changes.

You created a ColdFusion application page that retrieves and displays data from a database. At present, the output is raw and needs formatting. For more information, see Introduction to Retrieving and Formatting Data.

Reviewing the code

The results of the query appear on the page. The following table describes the highlighted code and its function:

Code

Description

<cfoutput query="EmpList">

Displays information retrieved in the EmpList query.

#EmpList.FirstName#, #EmpList.LastName#,

#EmpList.Salary#, #EmpList.Contract#

Displays the value of the FirstName, LastName, Salary, and Contract fields of each record, separated by commas and spaces.

<br>

Inserts a line break (go to the next line) after each record.

</cfoutput>

Ends the cfoutput block.

Query output notes and considerations

When outputting query results, keep in mind the following guidelines:

  • A cfquery must retrieve data before the cfoutput tag can display its results. Although you can include both on the same page, Adobe recommends that you place queries in ColdFusion components and output the results on a separate page. For more information, see Building and Using ColdFusion Components.

  • To output data from all the records of a query, specify the query name by using the query attribute in the cfoutput tag.

  • Columns must exist and be retrieved to the application to output their values.

  • Inside a cfoutput block that uses a cfquery attribute, you can prefix the query variables with the name of the query; for example, Emplist.FirstName.

  • As with other attributes, surround the query attribute value with double-quotation marks (").

  • As with any variables that you reference for output, surround column names with number signs (#) to tell ColdFusion to output the current values of the column.

  • Add a <br> tag to the end of the variable references so that ColdFusion starts a new line for each row that the query returns.