Output Table Data Verticaly
Get the Code for this Tutorial
View a Working Example of This Code
Comment on this Tutorial
Introduction
Dynamic Columns - Vertical Sorting
Sorts data top to bottom, left to right, sort of like a newspaper column.
The Query
Our example query is simply a SELECT
<cfquery name="get_names" datasource="#dsn#">
SELECT names
FROM cfml_example_data
ORDER BY names
</cfquery>
Set up Variables
We need to set up a few variables first. Set cols to equal how many columns you want to use. Rows calculates how many rows we will need by dividing recordcount by cols and rounding up to the nearest integer. This record will be the variable that tells us which record from the database to display. Even though we are sorting the data vertically, and we still need to write the cells from left to right, top to bottom to build the html.
<cfset cols = 4>
<cfset rows = ceiling(get_names.recordcount / cols)>
<cfset thisRecord = 1>
The Code
Start with a html table
<table>
Begin cfoutput, But we are not using the 'query' attribute. We will do a nested loop. The outer loop will go through the rows needed, and the inner loop will make the columns for each row.
<cfoutput>
Here we run a loop of our 'rows' variable. The loop repeats for each row. This is why we calculated how many rows we needed.
<cfloop from="1" to="#rows#" index="rowCount">
For each iteration of the 'rows' loop, start a new row.
<tr>
For each new row, start looping through our columns. We specified this as the 'cols' variable.
<cfloop from="1" to="#cols#" index="colCount">
Write out the html code for the table cells, and add a bit of code to make sure they are evenly sized.
<td width = "#int(100 / cols)#%">
Now we can output the data in the cell. Our variable thisRecord tells us which database record to put in each cell. We started at one, of course...
We need to check that thisRecord is less than or equal to the total number of database records (recordcount). If it is, show the record using brackets like an array. This lets us pull out any record from the query by row number.
<cfif thisRecord LTE get_names.recordcount>
#get_names.names[thisRecord]#<br/>
<cfelse>
</cfif>
Finish the current cell.
</td>
Set thisRecord so it will get the correct record on the next loop. Close the 'cols' loop. It will loop again on the next iteration of the 'rows' loop.
<cfset thisRecord = thisRecord + rows>
</cfloop>
At the end of each 'rows' loop set thisRecord to get the first Record for the new row. RowCount is the index of the 'rows' loop. If we are on the second row, we know that the first item in the 3rd row needs to be the third row in the query.
We also close the row at the end of each 'rows' loop.
<cfset thisRecord = rowCount+1>
</tr>
</cfloop>
Close the cfoutput tag, and finish up the html table tag.
</cfoutput>
</table>
____________
Tutorial Code
Download the Database<!--------------------------------------------
Kevin Sargent
October 14th 2005
http://www.lot-o-nothin.com/cfml/
Please Give Credit When Used.
--------------------------------------------->
<!--- the query --->
<cfquery name="get_names" datasource="#dsn#">
SELECT names
FROM cfml_example_data
ORDER BY names
</cfquery>
<!--- how many columns do we want? --->
<cfset cols = 4>
<!--- figure out how many rows our data will take up. --->
<cfset rows = ceiling(get_names.recordcount / cols)>
<!--- set thisRecord to 1 ---->
<cfset thisRecord = 1>
<!--- create the table --->
<table>
<cfoutput>
<!--- loop from 1 to rows. This part creates the rows for the table --->
<cfloop from="1" to="#rows#" index="rowCount">
<tr>
<!--- inside each row, create the cells for the row. --->
<cfloop from="1" to="#cols#" index="colCount">
<td width = "#int(100 / cols)#%">
<!--- if our thisRecord number is less than or equal to our number of records,
we need to display data from that row--->
<cfif thisRecord LTE get_names.recordcount>
<!--- a lot of people don't realize that you can get a row of data from a
ColdFusion query by row number using brakets like an array. This allows us
to get the rows from the database in any order we want. --->
#get_names.names[thisRecord]#<br/>
<!--- if it is not, fill in the empty cell with a space --->
<cfelse>
</cfif>
</td>
<!--- after each cell, update the thisRecord variable so that it gets the correct
record from the query next time around --->
<cfset thisRecord = thisRecord + rows>
</cfloop>
<!--- do the same thing after each new row also. --->
<cfset thisRecord = rowCount+1>
</tr>
</cfloop>
<!--- close cfoutput and finish the table. --->
</cfoutput>
</table>
