Grouping not quite there . . .

[Leonard_B]

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

[Miguel-F]

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.

[David_R1 · Accepted solution]

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>

[Leonard_B]

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