Queries | Inside Coldfusion MX

We use queries in ColdFusion to pull data out of backend databases to use within templates. A query object is a special structure within ColdFusion that holds the data in a query result set. A query object returns a row for each record in the result set. It also returns columns featuring the individual values.

There are different ways to create query objects within ColdFusion:

You can use ColdFusion tags such as CFQUERY and CFSTOREDPROC to retrieve data from the database. This creates the query object.
Other ColdFusion tags, such as CFDIRECTORY, CFFTP, CFLDAP, CFHTTP, CFREGISTRY (action="getall"), CFSEARCH, and CFPOP return values in a query object.
Query objects can be created using the QueryNew() function.
Query objects can be created as a result of some custom tag calls.

Creating Query Objects

In previous chapters , we covered how to query a data source using the CFQUERY tag. Later in this book, we discuss stored procedures in detail. A discussion of tags such as CFDIRECTORY, CFFTP, CFLDAP, and CFPOP are not appropriate at this point, so we'll save those for later as well.

We want to be able to manipulate a query object after we've created it, so let's take a look at the creation of query objects using the QueryNew() function.

The QueryNew() function creates an empty query object. When we call the QueryNew() function, we can specify a list of columns for the query object. The proper syntax is as follows:

 <cfset GetUsers=QueryNew("FirstName,LastName,Email")>

This is not the full extent of it though. Not only do we need to create the query object itself, but also we have to add rows and define the contents of the individual cells. To handle these operations, we call other query functions: QueryAddRow() and QuerySetCell(). The following is a full list of query functions:

IsQuery
QueryNew
QueryAddRow
QueryAddColumn
QuerySetCell
QuotedValueList
ValueList

To define how many rows are in a query object, we can use the QueryAddRow() function as follows:

 <cfset rows=QueryAddRow(GetUsers, 3)>

To define the values of each cell in a row, we can use the QuerySetCell() function. The QuerySetCell() function requires that you identify the query, column, value, and row number, as shown in the following code:

 <cfset var=QuerySetCell(GetUsers, "FirstName", "Neil", 1)>

Now we can take a look at the entire syntax of creating a query object. The code in Listing 7.2 sets the number of rows and sets the values of the cells in each row.

Listing 7.2 Creating a ColdFusion Query Object

 <html>  <head>           <title>Inside ColdFusion - Query Objects</title>  </head>  <body>  <table>    <tr>      <td>        <!--- We first create our query object --->        <cfset GetUsers = QueryNew("FirstName,LastName,Email")>        <!--- Now we set our values --->        <cfset rows=QueryAddRow(GetUsers, 3)>          <!--- Now we set our values --->            <cfset var=QuerySetCell(GetUsers, "FirstName", "Neil", 1)>            <cfset var=QuerySetCell(GetUsers, "LastName", "Ross", 1)>            <cfset var=QuerySetCell(GetUsers, "Email", "neil@codesweeper.com", 1)>            <cfset var=QuerySetCell(GetUsers, "FirstName", "John", 2)>            <cfset var=QuerySetCell(GetUsers, "LastName", "Cummings", 2)>            <cfset var=QuerySetCell(GetUsers, "Email", "john@mauzy-broadway.com", 2)>            <cfset var=QuerySetCell(GetUsers, "FirstName", "Robi", 3)>            <cfset var=QuerySetCell(GetUsers, "LastName", "Sen", 3)>            <cfset var=QuerySetCell(GetUsers, "Email", "r@granularity.com", 3)>              <!--- Now we do the output --->                My <cfif IsQuery(GetUsers)>query</cfif> data looks like this:                <br>                <table border="1" cellspacing="0">                  <tr><td>First Name</td><td>Last Name</td><td>Email Address</td></tr>                  <cfoutput query="GetUsers">                  <tr>                    <td>#GetUsers.FirstName#</td>                    <td>#GetUsers.LastName#</td>                    <td>#GetUsers.Email#</td>                  </tr>                  </cfoutput>                </table>                <br><br>                <cfset EmailList=ValueList(GetUsers.Email)>                The length of the list is: <cfoutput>#ListLen(EmailList)#</cfoutput>                <br><br>                This is the email list:<br>                <cfoutput>#EmailList#</cfoutput>              </td>            </tr>          </table>  </body>  </html>

Outputting Query Objects

As we've already covered using CFOUTPUT to loop through the result set of a query, we'll not go into detail here about how to do that. We will tell you, however, that this would be a really good place to start testing your CFDUMP tag.

When you run the code from Listing 7.2, you should get a result that looks a bit like Figure 7.2.

Figure 7.2. The query object content displayed.

graphics/07fig02.gif

Other query functions available to ColdFusion developers include the following:

IsQuery(). Returns TRUE if the specified value is a ColdFusion query object or FALSE if it is not.
```
 <cfif IsQuery(GetUsers)>GetUsers is a query</cfif> 
```
ValueList(). Returns a comma-delimited list of values for a specified column in a query result set.
```
 <cfset EmailList=ValueList(GetUsers.Email)> 
```

Query objects are some of the most frequently accessed complex variables with which you'll deal in ColdFusion development. We've shown you just how easy it is to create a list from a query result set, so why not move into lists next?