Coldfusion Tutorials

Spry Tutorials

Air Tutorials

SQL and Coldfusion

In the SQL Primer tutorial we were introduced to the structured query language (SQL). In this tutorial we are going to bring together most of what we have been introduced to thus far. We are going to connect to a database, create a query and finally output the results of our query first using the cfoutput tag. This tutorial will introduce you to the cfquery tag.

The CFQUERY tag

The CFQUERY tag allows us to connect to the database that we have previously created a datasource name for in the Coldfusion Administrator. In it's simplest form the CFQUERY tag takes two arguments as the example demonstrates. 

	<cfquery name="myquery" datasource="northwind">
		SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address 
		FROM Employees
	</cfquery>
  • The first attribute name="myquery" is how we will access the recordset that is returned, we must give it a name or we won't be able to output the information.
  • The second attribute datasource="northwind" tells the Coldfusion server the name of the datasource which then indicates what database we want to connect to.

Once we have our cfquery tag completed as above we will then be able to output the results of our query using the cfoutput or reference the information in the result set (record set).

Outputting the Results

As mentioned above we typically use the cfoutput tag to output the results of a query: 

<cfoutput query="myquery">
#EmployeeID#, #LastName#, #FirstName#, #Title#, #TitleOfCourtesy#, #BirthDate#, #HireDate#, #Address#<br>
</cfoutput>

We put the #'s around the query column names just like we would for any variable we want to output. We don't need to prefix our column names with the name of the query in this case because inside the cfoutput tag the default scope is the name of the query we specified. If we mixed in a local variable that had the same name of one of the query columns we would need to prefix our local variable with the correct scope in order for the variable NOT to be mistaken for the query column.

Query Variables

When you execute a query coldfusion creates a structure that stores not only the results of your query in the name you specify but it also creates additional elements in the structure that contains some useful information. In our example above we named our query myquery in this case we would have access to 3 additional variables.

  • myquery.recordcount - Great for finding out if your query returned anything as it holds the number of records returned by the query.
  • myquery.currentrow - Good if you use cfloop to output your query and want to do something on a specific row e.g. alternating row colors in a table for instance.
  • myquery.columnlist - Gives you the column names returned by the query, good if you want to automate the output of your query in the form of a table.

Conclusion

As you can see the basic usage of the cfquery tag is rather basic, there are a few additional attributes but they are for more advanced usage. In most cases we will use the basic attributes of the cfquery tag and not until we start paginating our records (spreading the information between multiple pages ) will we start using the other attributes in conjunction with more advanced attributes of the cfoutput tag.