Outputting with cfloop ?

Report · May 08, 2009

I have a table that contains information for 4 states. I need to output the information in a table with each state in its own <td>. I currently do this by using four different queries, one per state. If a new state is added, then I have to create a fifth query.

I know this is not the best way to do this but I do not know how to do it any other way. Would I use cfloop to dynamically generate so that I do not have to create a new query ?

Report · May 08, 2009

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.

Report · May 08, 2009

I am not sure I understand how to do this. What type of cfloop is it and when/how does it know to change to the next <td> ?

Report · May 08, 2009

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

Report · May 08, 2009

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

Report · May 09, 2009

Won't this give me all the states in one <td> ?

I have four states so I need four <td>, one for each state.

I currently have four queries, one for each state, thus one for each <td>.

Report · May 09, 2009

No it won't because inside each looping cycle you'll create a td.

2009/5/9, trojnfn <forums@adobe.com>:

>

Won't this give me all the states in one <td> ?

>

I have four states so I need four <td>, one for each state.

>

I currently have four queries, one for each state, thus one for each <td>.

>

Report · May 09, 2009

Sorry, I guess I misunderstood Ken's response. I did not know he included a cfloop in his example.

I am not sure what kind of cfloop to use and how it will will creae a different <td> for each state.

Can you provide some sample code ?

Thanks

Report · May 09, 2009

I hope this will help you, in your case you'll need to change the loop to a query loop.

<table border="1">
    <tr>
     <cfloop from="1" to="5" index="i">
          <td>
          I was created on the fly               
          </td>
     </cfloop>
  </tr>
<table>

Report · May 10, 2009

Like this:

<cfquery name="rsStates" datasource="myDatasource">
SELECT statename
FROM tblstates
ORDER BY statename ASC 
</cfquery>
<table border="0" cellspacing="0" cellpadding="5">
 <cfoutput query="rsStates">
  <tr>
   <td>#rsStates.statename#</td>
  </tr>
 </cfoutput>
</table>

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

Report · May 15, 2009

"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...

<tr>

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

</tr>

<tr>

</cfif>

...

</cfloop>

</tr>

<cfelse>

</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:

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.
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.
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.)