Creating Dynamic Columns part 2 (Read Part One here)

Earlier I showed you how to output your dynamic content across specified columns within a row.

It was a fairly straightforward example that showed the basics behind the trick.

However, being basic, it left a few things to be desired. Specifically, if you were outputting across 5 columns, and the final row only contained two records, the rest of the row was not populated with the 3 remaining <td></td>?s that it should have contained. 

If you were outputting this data in a bordered table, you?d see that there were no table cells in the bottom right corner?just a solid block.

The following example is more of a ?full tutorial? on a more proper way to specify a number of columns, and still maintain a ?well-formed? HTML document.

(you can see this code live at http://charlie.griefer.com/dyn_cols.cfm)

**********************************
<cfquery name="getData" datasource="xxx">
    SELECT myValue
    FROM dynamicCols
</cfquery>

<cfscript>
    if (NOT structKeyExists(form, 'cols')) form.cols = 4;
</cfscript>

<cfif getData.recordCount MOD form.cols>
    <cfset variables.rows = int(getData.recordCount/form.cols) + 1>
    <cfset variables.pads = form.cols - (getData.recordCount MOD form.cols)>

    <cfloop from="1" to="#variables.pads#" index="i">
        <cfset temp = queryAddRow(getData)>
        <cfset temp = querySetCell(getData, 'myValue', '&nbsp;')>
    </cfloop>
<cfelse>
    <cfset variables.rows = getData.recordCount/form.cols>
</cfif>

<cfset variables.thisRow = 1>
<cfset variables.newrow = false>

<form action="4cols.cfm" method="post">
Columns per row: 
<select name="cols" onchange="this.form.submit();" style="font-family:verdana; font-size:11px;">
    <cfloop from="1" to="10" index="i">
        <cfoutput><option value="#i#"<cfif i EQ form.cols> selected</cfif>>#i#</option></cfoutput>
    </cfloop>
</select>
</form>

<table style="border:0px; background-color:#000000;" cellspacing="1">
    <tr>
        <cfoutput query="getData">
            <cfif variables.newrow IS true><tr></cfif>
            <td style="background-color:##ffffff; text-align:right;">#myValue#</td>
            <cfif (getData.currentRow MOD form.cols EQ 0) AND (variables.thisROW LT variables.rows)>
               <tr>
               <cfset variables.newrow = true>
               <cfset variables.currentRow = variables.currentRow + 1>
            <cfelse>
               <cfset variables.newrow = false>
            </cfif>
        </cfoutput>
    </tr>
</table>
*********************************

Now to break it down:

<cfquery name="getData" datasource="xxx">
    SELECT myValue
    FROM dynamicCols
</cfquery>

This is our query. Previous example used a list, and looped over the list content (for the sake of simplicity). For this example, I wanted to show it as you?d be using it. This query will return 22 records. The values stored in the column myValue are the numbers 1 to 22 (inclusive).

<cfscript>
    if (NOT structKeyExists(form, 'cols')) form.cols = 4;
</cfscript>

This is the equivelant of <cfparam name=?form.cols? default=4>. I believe the syntax above executes a little bit faster, and as a general rule, I try to use <cfscript> over tags wherever I can (within reason).

<cfif getData.recordCount MOD form.cols>
    <cfset variables.rows = int(getData.recordCount/form.cols) + 1>
    <cfset variables.pads = form.cols - (getData.recordCount MOD form.cols)>

    <cfloop from="1" to="#variables.pads#" index="i">
        <cfset temp = queryAddRow(getData)>
        <cfset temp = querySetCell(getData, 'myValue', '&nbsp;')>
    </cfloop>
<cfelse>
    <cfset variables.rows = getData.recordCount/form.cols>
</cfif>

Here, we?re checking to see if the number of records returned evenly divisible by the number of columns that we want to display. If it is, then there isn?t much more we have to do. If it?s not, we need to determine how many ?pad? cells we?re going to need to add to the end of the final row.

variables.rows will be the number of rows that get output.. We determine this by dividing the recordCount of our query by the number of columns to display. We want a whole number, so we use CF?s int() function. We then need to add 1, which will be the ?incomplete? row to which we will add the extra <td>s.

variables.pads takes the MOD (remainder) of the recordcount divided by the specified number of columns. We then subtract that value from the specified number of columns, and the result tells us how many <td>?s we need to add.

Once we know how many ?pad? cells we need, we use the built-in queryAddRow() function to append the appropriate number of rows to the query (we want to end up with a recordcount that is evenly divisible by the column count). Once the additional records are added, we assign them a value of &nbsp; (non-breaking space).

The <cfelse> condition runs if the number of records in the recordcount is evenly divisible by the number of rows specified. e.g. if 20 records are returned, and 4 columns are specified, we will have 5 rows with 4 columns, and no need to pad (or add 1). So assuming this is the case, we set variables.rows to be the number of records divided by the columns value.

<cfset variables.thisRow = 1>
<cfset variables.newrow = false>

We need to keep track of which row we?re on (to know when we?ve hit the final row). So outside of any output, we set variables.thisRow to equal 1. We?ll increment it with each iteration of our output.

We also need to keep track of whether or not we closed a row (see the MOD condition towards the bottom of the table).  If we have, we will set variables.newrow to be true.  However, by default, and for every iteration of the output that does NOT create a new row, variables.newrow will evaluate to false.