I am used to outputting a ColdFusion query horizontally (going across then breaking into another row). However, I need to display a query vertically through columns but I can't figure out how to loop over it or where to break the rows. Please help.
Looping over a query and outputting it across rows of table is nice and easy because there is no calculations that need to be done. Well not true, you do have to figure out when to break in to a new row, but that's a simple MOD calculation. If you want to display a query vertically, things get much more sticky because you are outputting in a horizontal methodology, but the cell value has to be based on the vertical position and column offset.
In order to figure out what query value to output you need to figure out where you are in terms of output row and column:
<!--- To demonstrate, first we have to build a test query. For the purposes of demonstration, we are just going to build a simple, number-incrementing query so you can easily see where the values are output. ---> <cfset qNumbers = QueryNew( "" ) /> <!--- Populate the query. ---> <cfset QueryAddColumn( qNumbers, "number", "CF_SQL_VARCHAR", ListToArray( "1,2,3,4,5,6,7,8,9,0" ) ) /> <!--- In order for this to work, you have to explicitly define how many columns you want to output. ---> <cfset intColCount = 3 /> <!--- Based on the columns and the record count of the query, we can determine how many rows we are going to output. In this case, we are using the ceiling function as we will need to round up. ---> <cfset intRowCount = Ceiling( qNumbers.RecordCount / intColCount ) /> <!--- Output the query. Unlike a traditional horizontally outputted query, we cannot loop over the query itself as it does not have "proper" ordering. Instead, we have to loop over the rows and the columns and then grab the appropriate value out of the query. ---> <table> <tr> <!--- Loop over the rows, which we calculated above. ---> <cfloop index="intRow" from="1" to="#intRowCount#"> <!--- Loop over the columns (set above). ---> <cfloop index="intColumn" from="1" to="#intColCount#"> <!--- Here's where the magic happens. We need to figure out which query cell value we are getting based on the current row and column indexes. If you think about it, the beginning of each column begins with all the values from the previous columns. That means that each column begins with (COLUMN - 1) * the number of rows in a column. ---> <cfset intCellIndex = ((intRowCount * (intColumn - 1)) + intRow) /> <!--- Now, because we are outputting over a RxC output, its possible that we will have a cell that is outside of the record set. Check to see if the cell index is beyond the record count. ---> <td> <cfif (intCellIndex LTE qNumbers.RecordCount)> #qNumbers[ "number" ][ intCellIndex ]# <cfelse> .<br /> </cfif> </td> </cfloop> <!--- Check to see if we have any more rows to output. Once we hit the final row, we will let the </tr> post CFLoop handle the final close. ---> <cfif (intRow LT intRowCount)> </tr> <tr> </cfif> </cfloop> </tr> </table>
Running that gives us the following output:
[ 1 ][ 5 ][ 9 ]
[ 2 ][ 6 ][ 0 ]
[ 3 ][ 7 ][ . ]
[ 4 ][ 8 ][ . ]
It's a bit more work, but it gets the job done.
Want to use code from this post? Check out the license.