Skip to main content
L
Leonard_B
Inspiring
August 23, 2012
Answered

Grouping not quite there . . .

  • August 23, 2012
  • 2 replies
  • 752 views

Greetings,

First off, I have searched and reviewed previous posts for an answer but have not found anything that seemed to address the issue I am experiencing.

DB: MySQL

CF: 8

Below is query/code which displays only one FirefighterName. There could be anywhere from 4 to 6 Firefighters per station, per date. My desire is to display the FirefighterName horizontally under each station.

What am I missing here?

Thanks in advance for assistance

Leonard B

<=== My Query ===>

<cfquery name="rsShiftInfo" datasource="*****">

    SELECT

          recordID

        , ShiftDate

        , ShiftQuarter

        , ShiftMonth

        , FirefighterName

        , Station

        , PositionOne

        , PositionTwo

        , ShiftComment

    FROM

        firefightershiftassignments

    WHERE

        ShiftMonth = <cfqueryparam value="7" cfsqltype="cf_sql_integer">

    GROUP BY

        ShiftDate, Station

</cfquery>

<=== My Code ===>

<cfoutput query="rsShiftInfo" group="ShiftDate">

    <div style="clear: both; height: 250px;">

    <span>#DateFormat(ShiftDate,"mm/dd/yyyy - dddd")#</span><br />

    <cfoutput group="Station">

        <div style="padding-top: 15px;">

        Station: #Station#<br />

        #FirefighterName#

           </div>

    </cfoutput>

    </div>

</cfoutput>

<=== Current Output ===>

07/02/2012 - Monday

Station: 90
FirefighterName

Station: 96

FirefighterName

Station: 97
FirefighterName

Station: 99
FirefighterName

<=== Desired Output ===>

07/02/2012 - Monday

Station: 90

FirefighterName - FirefighterName - FirefighterName - FirefighterName - etc

Station: 96

FirefighterName- FirefighterName - FirefighterName - FirefighterName - etc

Station: 97
FirefighterName - FirefighterName - FirefighterName - FirefighterName - etc

Station: 99
FirefighterName - FirefighterName - FirefighterName - FirefighterName - etc

    This topic has been closed for replies.
    Correct answer David_R1

    Leonard,

    Miguel is right, your query as written would not return the data you seek.  Even if it did, your nested <cfoutput> would not display each individual firefighter like you want, either.

    In both cases you are misunderstanding the use of the grouping.  When you use "group by" in SQL, or "group" in cfoutput, you are asking for detail rows to be discarded from the results.  I suggest studying your SQL documentation and your CF documentation.

    Meanwhile, this should work better:

    <=== My Query ===>

    <cfquery name="rsShiftInfo" datasource="*****">

    SELECT

      recordID

    , ShiftDate

    , ShiftQuarter

    , ShiftMonth

    , FirefighterName

    , Station

    , PositionOne

    , PositionTwo

    , ShiftComment

    FROM

    firefightershiftassignments

    WHERE

    ShiftMonth = <cfqueryparam value="7" cfsqltype="cf_sql_integer">

    ORDER BY

    ShiftDate, Station

    </cfquery>

    <=== My Code ===>

    <cfoutput query="rsShiftInfo" group="ShiftDate">

    <div style="clear: both; height: 250px;">

    <span>#DateFormat(ShiftDate,"mm/dd/yyyy - dddd")#</span><br />

    <cfoutput group="Station">

    <div style="padding-top: 15px;">

    Station: #Station#<br />
    <cfoutput>#FirefighterName#</cfoutput>

    </div>

    </cfoutput>

    </div>

    </cfoutput>

    2 replies

    D
    David_R1Correct answer
    Inspiring
    August 23, 2012

    Leonard,

    Miguel is right, your query as written would not return the data you seek.  Even if it did, your nested <cfoutput> would not display each individual firefighter like you want, either.

    In both cases you are misunderstanding the use of the grouping.  When you use "group by" in SQL, or "group" in cfoutput, you are asking for detail rows to be discarded from the results.  I suggest studying your SQL documentation and your CF documentation.

    Meanwhile, this should work better:

    <=== My Query ===>

    <cfquery name="rsShiftInfo" datasource="*****">

    SELECT

      recordID

    , ShiftDate

    , ShiftQuarter

    , ShiftMonth

    , FirefighterName

    , Station

    , PositionOne

    , PositionTwo

    , ShiftComment

    FROM

    firefightershiftassignments

    WHERE

    ShiftMonth = <cfqueryparam value="7" cfsqltype="cf_sql_integer">

    ORDER BY

    ShiftDate, Station

    </cfquery>

    <=== My Code ===>

    <cfoutput query="rsShiftInfo" group="ShiftDate">

    <div style="clear: both; height: 250px;">

    <span>#DateFormat(ShiftDate,"mm/dd/yyyy - dddd")#</span><br />

    <cfoutput group="Station">

    <div style="padding-top: 15px;">

    Station: #Station#<br />
    <cfoutput>#FirefighterName#</cfoutput>

    </div>

    </cfoutput>

    </div>

    </cfoutput>

    L
    Leonard_BAuthor
    Inspiring
    August 23, 2012

    Hi David & Miguel,

    Thanks for your time in responding to my post it is appreciated. Following Miguel's suggestion with cfdump plus your suggestion / sample code, things are working as desired. It was definitely what you outlined in your sample. It has been awhile since I had to do output grouping, guess I did not clear out the cob webs before starting on the task.

    Once again thank you each for your time and information in assisting with a solution to my issue, it is greatly appreciated.

    Leonard B

    Miguel-F
    Miguel-F
    Inspiring
    August 23, 2012

    First thing to look at is your query.  Is it returning what you expect (more than one firefighter for a given station)?

    Just use <cfdump var="#rsShiftInfo#"> to see exactly what the query is returning.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices