Outputting with cfloop ?

"Holy COBOL, Batman!"

The way that we did such things, in the good ol' COBOL days (actually, since the days of Herman Hollerith...) was to sort the data by each of the major keys and then detect when the values changed.

For instance, if your query includes ORDER BY STATE, your_remaining_keys, it will now automagically produce output that is in the order you're now accustomed to, for each STATE.  Therefore, all of the rows for Alabama will be together, followed by all of the ones for Alaska, then all of the ones for Arizona, and so-on all the way to Wyoming, no matter how many STATEs there actually turn out to be.  Furthermore, once you have seen the last of Alabama, you know that you will never see anything from that state again.

Therefore, add logic in your loop that detects when the STATE changes from the previous record.  Something like this...

<cfloop ...>

   <cfif NOT IsDefined("previousState")>    <!--- this is the case the first time through --->

      <tr> 

     ... any other HTML output you need here ...

      <cfset previousState = looprecord.State>

   <cfelseif looprecord.State IS NOT previousState>

      </tr>

      <tr>

      <cfset previousState = looprecord.State>

   </cfif>

   ...

</cfloop>

<cfif IsDefined("previousState") >    <!--- loop ran at least once>

   </tr>

<cfelse>

    <!--- any handling you need for a completely-empty recordset --->

</cfif>

Since we (must!) know that the records are sorted by State, this logic detects when the State in the current record is different from the one in the previous record that has been encountered.  Note the following:

1. The first IF-case handles the very first record in the loop, when there is no previous record (and also, it has been established that the query is not completely empty).  This is the start of the first group of records, and the start of the group of records for this state.
   
2. The second IF-case handles a transition from one State value to the next.  This is both the end of the group of records represented by previousState, and the start of the group of records for the next one.
3. The last CFIF, after the loop, handles the case where we have reached the end of the records and, by virtue of the fact that "previousState" has some value, we know that the query was not completely empty.

This is the way that massive amounts of data are routinely handled in the "COBOL days" when a computer was a magical machine in a huge room in science-fiction movies.  It is also the way that Herman Hollerith devised to produce US Census results long before computers were invented.

This strategy will allow you to handle an unlimited number of states with no need for separate queries for each one.

If you need to handle "all 50 states, present or not," simply do a LEFT OUTER JOIN of your data against a table that has all possible state-values in it.  (Make very sure that all possible states do exist in this table.)  You can now test for transitions of this state-name column ... and by looking for NULL values in appropriate columns you can know whether it was joined to real data ("this state is present in the data") or not ("this state is missing"). A non-SQL version of the same idea would rely upon a ColdFusion array containing all of the known state-names in ascending order:  the array can be used to recognize and fill-in any gaps, during the output process previously described, eliminating the need for a join.  (It can also be used to detect the occurrence of a state-name that is not valid.)

have you tried something like this?

<cfoutput query="rsStates">
  <tr>
   <td>#rsStates.statename#</td>
  </tr>
</cfoutput>

Ken Ford
Adobe Community Expert - Dreamweaver/ColdFusion
Adobe Certified Expert - Dreamweaver CS3
Adobe Certified Expert - ColdFusion 8
Fordwebs, LLC
http://www.fordwebs.com
http://www.cfnoob.com

You may also want to check out the documentation for th 'group' property of the <cfoutput> tag.

Yes definitively so your page will work with an undertermined number of states. Do a query get all the states, then use cfloop generate the td as necesary and there you go.