Grouping cfoutput 4

[Guest_imported]

When running a query of a database of records, I can't figure out how to group the records so that there isn't multiple displays of the same variable. For example, I query for student name and want to see login times, lesson number etc. When I output the query, rather than see the name once with all of the login times following, I see the name, course code etc repeated for every instance in the database.

 

[GunJack]

You need to use the "order by" statement in your query first before you can group in your output. This is how I think you'll want it for your case.

<cfquery name=&quot;q1&quot; datasource=&quot;myDSN&quot;>
select studentName,courseCode,login,logout from table1
where ...
order by studentName asc, login asc
</cfquery>

<cfoutput query=&quot;q1&quot; group=&quot;studentName&quot;>
#studentname# #courseCode#

<cfoutput>#login# #logout#<p></cfoutput>

<hr>

</cfoutput>

Everything between the inner cfoutputs is displayed once for each record. Everything on the outside of these is displayed once for each &quot;grouping&quot;.

Hope this helps,
GJ

 

[cflearning]

Thanks. I'll give that a try.

 

[cflearning]

I haven't got this all resolved yet.
When I group by last name, the problem of the multiple names is addressed. But things like the course code and the lesson title continue to appear more than once in the output. If i put them in CFOUTPUT they really multiply. Is there a way of creating multiple groups?

ORDER BY
required_course_mast.&quot;course_code&quot; ASC,
required_course_mast.&quot;last_name&quot; ASC,
lesson_lookup_mast.&quot;lesson_title&quot; ASC

<cfoutput query=&quot;Tracking&quot;
group=&quot;last_name&quot;>
#last_name#
#cyb_course_code#
#cyb_course_desc#

 

[GunJack]

It shouldn't duplicate anything outside the inner cfoutput statements. While you can obviously have multiple sorts in the query, I don't know of anyway to do multiple groupings in the output. Can you post your complete query and output as I don't see why you would still have multiple entries for variables in the outer part of your ouptut.

GJ

 

[cflearning]

I've stripped out most of the table layout in the output, but you can see the complete query from here

<CFQUERY name=&quot;Tracking&quot;
datasource=&quot;datasource&quot;
dbtype=&quot;ODBC&quot;>
SELECT
cyb_required_course_mast.&quot;cyb_org_id&quot;, cyb_required_course_mast.&quot;cyb_last_name&quot;, cyb_required_course_mast.&quot;cyb_first_name&quot;, cyb_required_course_mast.&quot;cyb_course_code&quot;, cyb_required_course_mast.&quot;cyb_course_desc&quot;, cyb_required_course_mast.&quot;cyb_last_login_date&quot;, cyb_required_course_mast.&quot;cyb_last_discuss_date&quot;, cyb_required_course_mast.&quot;cyb_last_chat_date&quot;,
cyb_course_tracking_dtl.&quot;cyb_duration&quot;,
cyb_lesson_lookup_mast.&quot;cyb_lesson_title&quot;
FROM
{ oj (&quot;cyb_required_course_mast&quot; cyb_required_course_mast INNER JOIN &quot;cyb_course_tracking_dtl&quot; cyb_course_tracking_dtl ON
cyb_required_course_mast.&quot;cyb_student_id&quot; = cyb_course_tracking_dtl.&quot;cyb_student_id&quot; AND
cyb_required_course_mast.&quot;cyb_course_code&quot; = cyb_course_tracking_dtl.&quot;cyb_course_code&quot
INNER JOIN &quot;cyb_lesson_lookup_mast&quot; cyb_lesson_lookup_mast ON
cyb_course_tracking_dtl.&quot;cyb_lesson_num&quot; = cyb_lesson_lookup_mast.&quot;cyb_lesson_num&quot;}
WHERE
cyb_required_course_mast.&quot;cyb_org_id&quot; = 1
ORDER BY
cyb_required_course_mast.&quot;cyb_course_code&quot; ASC,
cyb_required_course_mast.&quot;cyb_last_name&quot; ASC,
cyb_lesson_lookup_mast.&quot;cyb_lesson_title&quot; ASC
</CFQUERY>

<cfoutput query=&quot;Tracking&quot;
group=&quot;cyb_last_name&quot;>
<TABLE ALIGN=BLEEDLEFT WIDTH=&quot;100%&quot; CELLSPACING=0 CELLPADDING=0 BORDER=0>
<TR>
size=3>#cyb_org_id#
</FONT> </FONT></TD>
<td colspan=27><b>#cyb_course_desc#</b></td>
cyb_last_name#,#cyb_first_name#></b></font></td>
</tr>
<tr valign=top>
<td colspan=2> </td>
<td colspan=7>Last Discussion:</td>
<td colspan=5>#cyb_last_discuss_date#</td>
<td colspan=4>Last Chat: </td>
<td colspan=9>#cyb_last_chat_date#</td>
<td align=right colspan=2>Last Login:</td>
<td> </td>
<td align=right colspan=8 NOWRAP>#cyb_last_login_date#</td>
</tr>
<tr valign=top>
<td colspan=3> </td>
<td colspan=19>#cyb_lesson_title#</td>
<td colspan=2> </td>
<td align=right colspan=5>Time in Lesson: </td>
<td> </td>
<td align=right colspan=3 NOWRAP>#cyb_duration#</td>
<td> </td>
<td colspan=2>minutes </td>
</tr>
<tr valign=top></tr>
</table>
</cfoutput>
</body>
</html>

 

[GunJack]

I think the problem is that your first sort field is not the field you're grouping on. I believe for this to work correctly, you need to have your &quot;grouping&quot; field be the first field in the sort order. If this isn't goint to correspond to how you need the data grouped, you may have to put some additional processing inside the loop to manually manage your groupings.

Hope this helps,
GJ

 

[cflearning]

Thanks again. I tried to group by the first sort field as well -- same results, single entry for course but multiple entries for name, etc.

 

[GunJack]

I didn't see it last time but you're missing the inner <cfoutput> tags. You need to use a second set of <cfoutput> tags around the fields which are different for each row. Without these, CF will output every field for each row. If you look at my first post, studentname and coursecode are outside of the inner <cfoutput> tags so they only get generated once for each grouping. Inside the second set of <cfoutput> tags, login and logout get displayed for each row in the recordset.

GJ

 

[cflearning]

I tried it with inner cfoutput tags as well before sending my last response. Adding cfoutput around student name, for example, results in the student name being duplicated. Adding it around cyb_course_code duplicates the course code and so on.

 

[GunJack]

I think somehow we're not talking about the same thing. I created a test table and sample code to do what I think you're talking about. Let me describe it and tell me if this is what you have and need to do.

I made a table with name and score. I put 4 records in for two students and the rows look like this:

John 1
John 2
John 3
John 4
Mary 1
Mary 2
Mary 3
Mary 4

Here's my query:

<cfquery name=&quot;info&quot; datasource=&quot;test&quot;>
select * from students
order by name asc, score asc
</cfquery>

and my ouput statement:

<cfoutput query=&quot;info&quot; group=&quot;name&quot;>

#name#<p>

<cfoutput> &nbsp; &nbsp; &nbsp; #score#<p></cfoutput>

</cfoutput>

This yields the following output:

John
1
2
3
4

Mary
1
2
3
4

Is this along the same lines of what you're trying to do? I know it's very simplified but if we start with this and start adding your fields and such to it, we should get to a point to where it does what you need.

GJ

 

[viv77]

Hi, hope this is helpful.

Actually what we're having here is multiple levels of nesting (must sort by the fileds that are being grouped).
Let's do this (i tried this before and it works):

ORDER BY last_name, course_code, course_desc

<cfoutput query=&quot;theQuery&quot; group=&quot;last_name&quot;>
#last_name#
<cfoutput group=&quot;course_code>
#cyb_course_code#: #cyb_course_desc#
<cfoutput>
.... the remaining fields
</cfoutput>
</cfoutput>
</cfoutput>

 

[GunJack]

Thanks for the tip Viv, all this time I had been under the impression CF would only allow one level of grouping. Do you know if this was a recent change under CF 4? The 3 level grouping is really what I think he needs but I thought he was going to have to do it manually for the third level. Your solution should be very close.

Thx,
GJ

 

[kathryn]

Interestingly, I have just created a page that has 16 levels of grouping. I have been using .rtf pages to output reports and for formatting purposes, I needed that many groups. Worked like a charm.
Kathryn

 

[cflearning]

Thanks to all who've contributed. Yes, the levels of grouping appears to be the solution. The data is now appearing the correct number of times. Now on to wrestle with getting it to display in the tables...

Thanks again,
cflearning.