Each time you query a database with the cfquery tag, you get the data (the recordset) and the query variables; together they make up the query object. The following table describes the query variables, which are sometimes called query properties:
Variable |
Description |
|---|---|
RecordCount |
The total number of records returned by the query. |
ColumnList |
A comma-delimited list of the query columns, in alphabetical order. |
SQL |
The SQL statement executed. |
Cached |
Whether the query was cached. |
SQLParameters |
Ordered array of cfqueryparam values. |
ExecutionTime |
Cumulative time required to process the query, in milliseconds. |
In your CFML code, use these variables as if they are columns in a database table. Use the result attribute to specify the name of the structure that ColdFusion populates with these variables. You then use that structure name to refer to the query variables as the following example shows:
Output information about the query on your page
Edit emplist.cfm so that it appears as follows:
<cfset Emp_ID = 1>
<cfquery name="EmpList" datasource="cfdocexamples" result="tmpResult">
SELECT FirstName, LastName, Salary, Contract
FROM Employee
WHERE Emp_ID = <cfqueryPARAM value = "#Emp_ID#"
CFSQLType = "CF_SQL_INTEGER">
</cfquery>
<cfoutput query="EmpList">
#EmpList.FirstName#, #EmpList.LastName#, #EmpList.Salary#, #EmpList.Contract#<br>
</cfoutput> <br>
<cfoutput>
The query returned #tmpResult.RecordCount# records.<br>
The query columns are:#tmpResult.ColumnList#.<br>
The SQL is #tmpResult.SQL#.<br>
Whether the query was cached: #tmpResult.Cached#.<br>
Query execution time: #tmpResult.ExecutionTime#.<br>
</cfoutput>
<cfdump var="#tmpResult.SQLParameters#">- Save the file and view it in your web browser:The number of employees now appears below the list of employees. If necessary, refresh your browser and scroll to see the RecordCount output.
Reviewing the code
You now display the number of records retrieved in the query. The following table describes the code and its function:
Code |
Description |
|---|---|
<cfoutput> |
Displays what follows. |
The query returned |
Displays the text "The query returned". |
#EmpList.RecordCount# |
Displays the number of records retrieved in the EmpList query. |
records. |
Displays the text "records." |
</cfoutput> |
Ends the cfoutput block. |
Query variable notes and considerations
When using query variables, keep in mind the following guidelines:
- Reference the query variable within a cfoutput block so that ColdFusion outputs the query variable value to the page.
- Surround the query variable reference with number signs (#) so that ColdFusion knows to replace the variable name with its current value.
- Do not use the cfoutput tag query attribute when you output the RecordCount or ColumnList property. If you do, you get one copy of the output for each row. Instead, prefix the variable with the name of the query.
