Grouping problem

[Bob-El]

Hi Guys,

I have created two related tables that contain the following info:
Table1:
speakerID
speakerName
speakerReview
speakerImage

Table 2:
venueID
speakerID
venue
eventDate

I have managed to output data by grouping it with date and venue. But I can't in anyway get the data to show events in one row. i.e, if I've got 4 speakers performing in the same venue on the same date, I get the result of each speaker name individually listed in each row, four rows down. I want them to be in the same row sorted by the date and venue.

Any help would be very much appreciated..

Thanks
Rob

[Guest]

I'm assuming you want something like this? Do you have an event id somewhere?

date 1 venue 1 speaker 1 speaker 2
date 2 venue 2 speaker 2 speaker 3 speaker 5
date 2 venue 1 speaker 7 speaker 8

[Bob-El]

Hello J Deline,

That's exactly what I need. The following code is what I'm working with:

<table id="tableLayout">
<cfoutput query="rsPrograms" group="eventdate">
<tr>
<td>#LSDateFormat(rsPrograms.eventDate)#</td>
<td> </td>
</tr>
<cfoutput>
<tr>
<td> </td>
<td><a href="details.cfm?ID=#rsPrograms.Speakers.speakerID#">#rsPrograms.speakerName#</a></td>

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

& No, I haven't got any eventID anywhere..

Many thanks for your help

Rob

[Guest]

Try the attached code....
(Note that data must be sorted by: eventDate, venue, then speakerName.)

[Bob-El]

Hey Mike,

I can't thank you enough, you're a star. Many thanks for your help....

Thank you

Rob

[sarawilliams]

It sounds like you’re really close the issue is just how the data is grouped before output. You’ll probably need to aggregate the speakers for each date/venue into a single row, either by using GROUP_CONCAT (if your DB supports it) or by restructuring the loop in your code to collect speakers before printing the row. Basically, group first, then output once. Hope that helps point you in the right direction.