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!

Build a Query Manually

If you really want to build a query manually, you can use a combination of several functions to do so. To start with, we’ll need to discuss some of the functions that are built into Coldfusion for manipulating queries.

First of all, we have to create a query object. This is easily accomplished with the QueryNew() function. This function takes a comma delimited list of the columns that need to be in the query.

<cfset myQuery = QueryNew("id, name, username, password")>

Now, we have an empty query. It lists the columns in the function parameter, but lists no rows at all. A query without data isn’t much use to us, so we’ll add some data using the QuerySetCell() function. This function takes the name of the query, the name of the column, the value of the column, and the row number to insert this information into. We can add our first user like so:

<cfset temp = QuerySetCell(myQuery, "id", "1", 1)>  

<cfset temp = QuerySetCell(myQuery, "name", "John Doe", 1)>  

<cfset temp = QuerySetCell(myQuery, "username", "JDoe", 1)>  

<cfset temp = QuerySetCell(myQuery, "password", "test", 1)>

After this, we can output our query to the screen and see that it really is a query object with the data we’ve entered.

<cfoutput query="myQuery">  

  #id# : #name# : #username# : #password#<br>  

</cfoutput>

Now, you may be thinking that you don’t see much of a point in creating your own query, and, in fact, in most cases you won’t need this. But, here’s a scenario in which you might find some use for this functionality. I won’t provide any code for this particular case, since it’s actually fairly involved, but I’m sure you can dream up all sorts of ways to apply this technique.

Imagine that you are building an online store for an affiliate that provides product streams via a Web service (one very large affiliate comes to mind). When you call the Web service, an XML document is returned to you. When XML documents have been parsed, they are returned as structures nested inside arrays nested inside structures, etcetera, etcetera, etcetera.

We can create a query object that will be easier to manipulate than a convoluted series of structures and arrays using the above methodology. In fact, I just happened to find this tutorial, which will show you how to accomplish this particular task (it doesn’t use Amazon’s Web services, but the concept is the same and easily portable).

You can also add a column to a query using the QueryAddColumn() function. For this function, you specify the query name, the column name, and the name of an array that contains the data with which you want to populate the column. To prove that this actually does happen in real life, take a look at this forum post. This is a real life situation where the QueryAddColumn() function could come in handy (ignore any syntax errors in there. It was whipped up on the fly without testing…). So, there are definitely real life situations where you may need to use these functions.

Querying a Query

Sometimes, you may need to pull a lot of detailed data from a database and then retrieve aggregate data based on the detailed information. In this case, Coldfusion’s ability to use a query as a data source comes in quite handy. Now, as forum member mrhatch pointed out to me in the previously referenced forum thread, this functionality is referred to as CFSQL (I just called it a query of a query before).

Let’s take a look at a situation in which this might be useful. In our existence as programmers, we often find ourselves creating reports. Yes, we hate to do it, but it’s a necessary evil if we want to pay our bills. In many cases, users want the ability to see aggregate data as well as detailed information. In this case, we have a few options.

1. We can get a query containing the details and, as we output the query, we can pull aggregate data from the database using an identifier contained within the detail query.


2. We can output the query and manually compile our aggregate results as we do so, performing any needed calculations before finally outputting to the screen.


3. We can use CFSQL to avoid both of the above scenarios and, at the same time, retrieve the needed summary information.

For our tutorial, we’ll go with the third option since, of course, it demonstrates the point here. We’ll say that we have a query contains the following data:

This data is in a query called GetDetails and it shows a detailed listing of both our company’s sales people, the orders they fulfilled, and the dates and clients for which the orders were fulfilled. What we want to see is a prettier version of this, in which each sales person’s name is a header and, beneath that, appears a list of their sales. Then, at the bottom of each section, we want to see the sales person’s total sales.

Challenge: Try to create the query above using only the QueryNew, QueryAddRow, and QuerySetCell functions. This way, you don’t have to create a database, a data source, and the table in order to follow along with the other examples.

Let’s start by outputting our query. This will give us the detailed listing of how our sales guys did:

<cfset curUserID = GetDetails.UserID>  

<cfoutput query="GetDetails">  

   <cfif CurrentRow lt RecordCount>  

      <cfset nextUser = GetDetails.UserID[CurrentRow + 1]>  

   <cfelse>  

      <cfset nextUser = "">  

   </cfif>  

   <cfif curUserID neq UserID or CurrentRow eq 1>  

      <hr>  

      #UserID# #UserName# (#FullName#) : <br>  

   </cfif>  

   <blockquote>  

      #OrderID# - #CustomerName# was sent on #DateFormat(SentDate, "mm/dd/yyyy")# #TimeFormat(SentDate, "hh:mm:ss tt")#<br>  

   </blockquote>  

   <cfif NextUserID neq UserID>  

      <cfquery name="GetCount" dbtype="query">  

         SELECT Count(OrderID) AS OrderCount  

         FROM GetDetails  

         WHERE UserID = #UserID#  

      </cfquery>  

      <blockquote>  

         <strong>User Total: #GetCount.OrderCount#</strong>  

      </blockquote>  

   </cfif>  

   <cfset curUserID = UserID>  

</cfoutput>

Okay, there’s a lot of code here to cover, so let’s get right into it. First, we only want to output each sales person’s name and ID once. So, we have to track what the current user ID is, and what the next user ID is. We track our current user with the curUserID variable. We then dive head-first into our query.

The first CFIF statement looks to see if we’re on the last record in the query. If we are, our next user is a null string. If we’re not, we treat the UserID column as an array, and we get the value of the UserID column in the next row.

Now, we want to know if it’s time to display the user’s name. We know that, if this is the first row in the query, we’ll need to. But, if the curUserID variable is not the same as the UserID variable (which comes straight from the query), this means we have advanced to another user’s records. At this point, I should emphasize the importance of ordering your columns by your identifier, in this case UserID. If we are on another user’s records, we output that person’s name, user name, and user ID.

Now, we simply display the order ID, customer name, and the date on which the order was sent. Notice that these bits of data are displayed no matter where we are in the query.

Next, we get to the interesting part: we look to see if this is the last row for this user. If it is, we use the magic CFSQL. Notice a couple of odd things about this query. First of all, we don’t specify the “datasource” attribute; we specify the “dbtype” attribute and assign it the “query” value. Second, we aren’t selecting from a table; we’re selecting from a query. But, when you think about it, there really isn’t a big difference between a table and a query. A query has the same structure as a table, with rows and columns.

Now, we can limit our record set from the CFSQL query just as we might limit our record set from a MySQL database or a SQL Server database. We use the WHERE clause to specify that we only want to get the current user’s records. Then, we output the record count of the query. Finally, we set the curUserID variable to the current UserID from the query.

Using Coldfusion 5, I’ve personally had shaky results using aggregate functions like COUNT in combination with WHERE clauses in CFSQL queries. Coldfusion MX seems to have rectified the problem, since I tested all of this code on CFMX 6.1. If you’re using Coldfusion 5, you can just select all or a single field from the query, and use the (QueryName).RecordCount variable to figure out how many sales this user had, for instance, or whatever it is you’re trying to figure out.

Conclusion

Queries are more than just the results you get from a database. In Coldfusion, queries offer very simple ways to display and manipulate data. You can convert other data structures (like XML documents) into queries, and display them in a much simpler fashion. You can glean aggregate data from detailed queries without additional database hits using CFSQL. You can access specific rows and columns in a query by treating it as a structure containing a series of arrays. You can display a query dump using the ColumnList variable attached to each query. And you can get a listing of directories and files and access it just as you would a query.

That concludes our Introduction to Coldfusion Data Structures series. There are, of course, all sorts of data structures that can be put to excellent use by a creative developer, and I hope that this series has helped you to see some of the ways in which Coldfusion can be ever-so-easily manipulated to meet today’s business challenges.

Frequently Asked Questions (FAQs) about Data Structures and Queries