CODEWITHSUNDEEP
coldfusion
David Brierton
David Brierton

Reputation: 7397

CF Location queries looping issue

I am trying to figure out how to run queries through a MS sql database using ColdFusion in order to make a table that keeps track of Location, Percent of Total Checklists, and Location Total.

I am having trouble looping to show my locations only once and to run the totals for each location. I am not sure why my table is adding all of these lines like the picture below, any help with this would be greatly appreciated!

<cfset result = {} /> 
<cftry> 
    <cfquery datasource="#application.dsn#" name="GetLocationInfo">
        SELECT *
        FROM cl_checklists
    </cfquery>

    <cfcatch type="any"> 
        <cfset result.error = CFCATCH.message > 
        <cfset result.detail = CFCATCH.detail > 
    </cfcatch> 
</cftry> 

<table border="1" id="Checklist_Stats">
    <thead>
        <th><strong>Location</strong></th>
        <th><strong>Percent of Total Checklists</strong></th>
        <th><strong>Location Total</strong></th> 
    </thead>
    <tbody>
    <cfquery name="allLocCode" dbtype="query">
        SELECT DISTINCT trans_location, COUNT(*) AS locCntr FROM GetLocationInfo GROUP BY trans_location ORDER BY trans_location 
    </cfquery>
      <cfloop query="allLocCode">
      <cfset thisLocationName = trim(allLocCode.trans_location) />
      <cfoutput query="allLocCode">
          <tr>
              <td><strong>#thisLocationName#</strong></td>
              <td></td>
              <td></td>
          </tr>
          <cfset thisLocationName = "" />
      </cfoutput>
      </cfloop>
    </tbody>
    <!--- Total of All Sum of each column --->
    <tr>
      <td><strong>Total</strong></td>
      <td></td>
      <td></td>
    </tr>
</table>

enter image description here

Upvotes: 0

Views: 44

Answers (2)

Matt Busche
Matt Busche

Reputation: 14333

You only need to loop through the query once. Remove the additional cfoutput

<cfoutput query="allLocCode">
  <cfset thisLocationName = trim(allLocCode.trans_location) />
  <tr>
    <td><strong>#thisLocationName#</strong></td>
    <td></td>
    <td></td>
  </tr>
 </cfoutput>

Upvotes: 3

Stefan Braun
Stefan Braun

Reputation: 400

Your code creates a nested loop.

  <cfloop query="allLocCode"> // loop over all items in the query
  <cfset thisLocationName = trim(allLocCode.trans_location) />
  <cfoutput query="allLocCode"> // loop again over all items in the query
      <tr>
          <td><strong>#thisLocationName#</strong></td> // print the string
          <td></td>
          <td></td>
      </tr>
      <cfset thisLocationName = "" /> // empties the string, hence next rows will be empty
  </cfoutput>
  </cfloop>

Change the line <cfoutput query="allLocCode"> to <cfoutput>.

Upvotes: 2

Related Questions