Outputting and grouping records in multiple column format issue

[brianism]

Hi All,

I am banging my head off the wall with this one. I have a query that displays the output in a two column format, it works great.

I run into an issue when I try to use "group' so the field DSC1 does not show duplicates.  It turns the output into 2 column, 3 column and 4 column with blank spots throughout.

Anybody know how I can get it to display 2 columns of output and not show duplicate DSC1 fields? Here are examples of what happens:

Example 1 of what it does without group (note apples is twice, can't have that) :

apples     apples

oranges  peaches

banannas  grapes

What I need to do (only show each DSC1 once and continue with the two column format):

apples   oranges

peaches  banannas

grapes

Here is the coding I am using:

<cfquery name="getProduct" datasource="mydb">
    SELECT * FROM ecitm
    WHERE DSC1 <> ' '
order by DSC1

</cfquery>

<!---start the table to output 2 columns of data--->

<table border="0" width="90%" border="0" align="center" cellpadding="5" cellspacing="5">
   
    <cfset newrow = false

    <tr>
    <cfoutput query="getProduct" group="DSC1">
    
    <cfif newrow EQ "true">
        <tr>
    </cfif>
        <td>
        #DSC1#
         <cfif DSC2 NEQ "">
         <br>
         #DSC2#
         </cfif>

<br>
        <a href="DetailsList.cfm?ID=#getProduct.ID#&litm=#getProduct.LITM#">VIEW DETAIL</a>
       </td>
       
        <cfif getProduct.currentRow MOD 2 EQ 0>
                </tr>
                <cfset newrow = true>
        <cfelse>
                <cfset newrow = false>
        </cfif>

   
    </cfoutput>
    </tr>
</table>

[ilssac · Accepted solution]

The currentRow property is always going to count the number or records in the record set loop, whether you output them or not.

Since you are not output values for every record in the record set, you need to count the itterations yourself.

Set an itteration counter before the <cfoutput...> loop.

<cfset itt = 0>

Update the counter in the top of the <cfoutput...> loop.

<cfset itt = itt + 1>

OR on newer versions of CF

<cfset itt++>

Reset the counter in the true clause of the <cfif...> block where you also set the new row.>

<cfset itt = 0>

Then use the iit variable in place of the currentRow property to determine how many items have been displayed.

**** OR ****

You could just do a simple SQL command to not get duplicates in the first place, and then use a simple <cfoutput...> loop without any of the extra logic and grouping you are doing.

<cfquery name="getProduct" datasource="mydb">
     
SELECT DISTINCT *
     FROM ecitm
     WHERE DSC1 <> ' '
     ORDER BY DSC1
</cfquery>
<tr>
<cfoutput query="getProduct">
     <td>#DSC1#</td>
     <cfif getProduct.currentRow MOD 2 EQ 0>
</tr><tr>
    </cfif>
</cfoutput>
</tr>

[brianism]

Ahhh, you are a genius!  Thank you, I used your itteration method and that works exactly how I need it to.  Below is the code I use:

<cfquery name="getProduct" datasource="mydb">
    SELECT * FROM ecitm
    WHERE DSC1 <> ' '
order by DSC1

</cfquery>

<table border="0" width="90%" border="0" align="center" cellpadding="5" cellspacing="5">
   
    <cfset newrow = false>

    <cfset itt = 0>
    <tr>
    <cfoutput query="getProduct" group="DSC1" StartRow="#StartRow#" MAXROWS="#MaxRows#">
     <cfset itt = itt + 1>
    
    <cfif newrow EQ "true">
        <tr>
    </cfif>
        <td>
        #DSC1#
         <cfif DSC2 NEQ "">
         <br>
         <em style="color: gray;">#DSC2#</em>
         </cfif>
         <br>
        <a href="DetailsList.cfm?ID=#getProduct.ID#&litm=#getProduct.LITM#">VIEW DETAIL</a>
       
        </td>
       
        <cfif itt MOD 2 EQ 0>
                </tr>
                <cfset newrow = true>
                <cfset itt = 0>
        <cfelse>
                <cfset newrow = false>
        </cfif>
       
       
   
    </cfoutput>
    </tr>
</table>

[ilssac]

If you care, reviewing my code, I just realized that reseting the itterator variable in the <cfif...> block is unnecessary.  Since you are basing the if branch in whether the itterator is even or odd, then reseting it to zero each time the value is even is an unnecessary microsecond or two of code processing.

<cfif itt MOD 2 EQ 0>

  </tr>            

  <cfset newrow = true>

  <cfset itt = 0>

<cfelse>