Introduction to Coldfusion Data Structures Part IV – Queries

Okay, this is definitely the last you'll hear from me about data structures. I have, however, saved one of the most useful data structures for last. Queries are an important part of Web development, whether you're programming in ASP, JSP, PHP, or Coldfusion. Sometimes, they're referred to as result sets, record sets, or, in the case of Coldfusion, queries.

Throughout this article, we'll look at a few different topics related to queries, including database queries, file system queries, building a query manually, looping through them, and how you can actually run a query against an existing query. So, without further ado, let's get started.

Database Queries

The most common way to get a query object is by querying a database using the Structured Query Language (SQL). To do this, we use the CFQUERY tag to retrieve our result set. It's very simple, but you'll first need to set up a data source using the sample database in the referenced article. Once you've got your data source set up, all you have to do is use a CFQUERY command with a bit of SQL in it:

<cfquery name="GetUsers" datasource="MyDSN">
 SELECT *
 FROM Users
</cfquery>

Now, this query will instruct Coldfusion to access the database that is referenced by the data source name "MyDSN" and to open the table "Users". It will then retrieve all the rows and all the columns in that table. So, we now have our query object. It's time to explore the ways in which we can use it.

Using The Query

The most common way to use a query is to loop over it or output it. You can do this using the QUERY attribute of either the CFOUTPUT or CFLOOP tags. For example, let's say we want to display everything in the table on our page. It's very simple with Coldfusion. All we have to do is:

<cfoutput query="GetUsers">
 #Name#<br>
</cfoutput>

This will go through every row in the query and display each name on a separate line. There are a few different options that can be specified for the CFOUTPUT tag when you use the QUERY attribute. You can easily specify which row you want to start on, and how many rows to display. So, if we want to display only the first row, we can do this:

<cfoutput query="GetUsers" maxrows="1">
 #Name#<br>
</cfoutput>

Or, we can start at the second row:

<cfoutput query="GetUsers" startrow="2">
 #Name#
</cfoutput>

The two attributes can be used together to display a small section of a query at a time. This makes paging through queries extremely simple, with a little forethought and creativity.

Now, the CFOUTPUT tag works well to display information, but if you're simply performing some sort of logic on the rows in the query, you might want to use CFLOOP for better performance:

<cfloop query="GetUsers">
 <cfif Name eq "John Doe">
   <cfquery name="UpdateUser" datasource="MyDSN">
 UPDATE Users SET
 State = 'TX'
 WHERE ID = #ID#
   </cfquery>
 </cfif>
</cfloop>

This will loop over the query and move John Doe to Texas, but leave everyone else where they are.

Now, when you run a query, Coldfusion attaches to the query several variables that you can access. First, there's the RecordCount variable. This gives you a count of all the records in the query. You can use it in logic like:

<cfif GetUsers.RecordCount gt 0>
 <!--- Do some logic here with your users query --->
</cfif>

There's also the CurrentRow variable. CurrentRow comes in very handy and in most cases, it keeps you from having to create your own counter. For example, let's say that I'm outputting my users in a table and I want to alternate the row colors for readability. Here's how I'd normally do this:

<table>
 <cfoutput query="GetUsers">
   <cfset bgcolor = "##FFFFF">
   <cfif CurrentRow MOD 2>
       <cfset bgcolor = "##CCCCCC">
   </cfif>
   <tr bgcolor="#bgcolor#">
     <td>#Name#</td>
     <td>#State#</td>
   </tr>
 </cfoutput>
</table>

Here, we set the variable every time we iterate over the loop so that the background color is white. Then, if the modulus of the current row divided by 2 is 1, we make the background color gray. The first time through the loop, CurrentRow will be equal to 1, so the background color will be gray.

Another built-in variable you may find useful is the ColumnList variable. This is a comma delimited list of all the columns returned with a given query. So, I could run a query and dynamically output a table with the columns like this:

<table border="1">
 <tr>
   <cfloop list="#GetUsers.ColumnName#" index="column">
     <cfoutput><th>#column#</th></cfoutput>
   </cfloop>
 </tr>
 <cfoutput query="GetUsers">
   <tr>
     <cfloop list="#GetUsers.ColumnName#" index="column">
       <td>#Evaluate(column)#</td>
     </cfloop>
   </tr>
 </cfoutput>
</table>

This will give you a nice, neat little table in which the column names appeared across the top of the table, and the data displayed in the appropriate column. The awesome thing about this is that you can use it to display any query you want. Just change the query name, and there you have it.

These are some of the most common uses for Coldfusion's query object. You'll probably find yourself using one of the above methods most of the time. But now, let's look at a few other ways we can get and use query objects.

Directory Listings and Query Objects

This is a short section, but it's valuable nonetheless. Every programming language I've ever encountered had some means of access the file system. This includes creating, writing to, deleting, moving, copying, and reading files, and creating, deleting, renaming, and listing directories.

Well, when we want to get a list of directories in Coldfusion, we don't have to sweat too much to do the job! First, we simply get a directory listing:

<cfdirectory action="list" directory="#GetDirectoryFromPath(GetTemplatePath())#" name="CurrentDir">

This gives us a list of all the files in the directory with the current template. The name attribute specifies the name of the query object that will be returned. So, we can output the directory listing simply by looping over the query:

<cfoutput query="CurrentDir">
 #Name#<br>
</cfoutput>

Now, this query object contains several columns. You can access these with the ColumnList variable if you want, but I'll go ahead and tell you what columns they are.

• Name
• Size
• Type
• DateLastModified
• Attributes
• Mode (for *IX systems only, i.e. "755", "466", etc.)

There's yet another way you can get a query object for use in your application. But, you don't have to have access to the file system or a database to get a query object. You can build your own if you want!