Loop question I think

I need to output an activity listing. So far all output is okay (name, title, description) Each activity can have multiple dates, for which I have a seperate table(activity_dates) and this is where I’m having problems. If is just output the value from the query, I get just one date belonging to that specific activity.I think that I need to loop over the dates, but have no idea what kind of loop I need to use. I tried a from to loop the following way:


<cfloop index="i" from="1" to="#getDates.recordCount#">
#activity_date[i]#
</cfloop><br>

But obviously I get all dates for each event. What kind of loop shoud I use or should I take a complete diffeent approach?

use a nested CFOUTPUT

if you show me the query, and the raw results (unformatted), i’ll mock up the CF code for you

Thanks a lot Rudy, this is doing my head in:


<!--- Session.language is for testing only --->
<cfset Session.language = "eng" >

<cfquery name="getDates" datasource="#Application.dsn#">
	SELECT
  			AL.activity_id
      ,	A.activity_name
      ,	A.language_abbr
  		,	AD.activity_date
  FROM
  			activity_list AL
  INNER
  	JOIN 	activities A
    	ON	AL.activity_id = A.activity_id
     AND 	A.language_abbr = <cfqueryparam cfsqltype="cf_sql_char" value="#Trim( Session.language )#" />
  INNER
  	JOIN 	activity_dates AD
    	ON	AL.activity_id = AD.activity_id	
</cfquery>

And the results should be someting like:


Mycenaean Armour and Horse Grave
05/05, 15/05, 24/05

I hope this is what you ment with raw results.

Thank you again Rudy

actually, the raw results would look something like this –


[b]activity_id activity_name language_abbr activity_date[/b]
    9       Mycenaean ...      EN       2011-05-05 
    9       Mycenaean ...      EN       2011-05-15 
    9       Mycenaean ...      EN       2011-05-24 
   37       Knossos .....      EN       2011-09-09
   37       Knossos .....      EN       2011-09-11

see what i mean? that’s what the query returns, including the duplication in the activity columns due to the one-to-many relationship of activity to dates

of course, to ensure that this works correctly, you really must have an ORDER BY in your query –


ORDER
    BY AL.activity_id
     , AD.activity_date

okay, now we can use a nested CFOUTPUT

the outer CFOUTPUT detects the activity groupings based on the GROUP= parameter

whatever’s inside the outer CFOUTPUT will execute once for each group of rows

it first prints the activity stuff, then sets up the variable string which collects the dates for the group

the inner CFOUTPUT handles the activity dates, but notice that they aren’t printed until after the group is finished (which is detected either by the first row of the next group, or the end of the results)

i’ll use ListAppend to produce the nice formatted list that you wanted


<CFOUTPUT QUERY="getdates" GROUP="activity_id">
<p>#getdates.activity_name# <!--- and any other activity data --->
<CFSET variables.datestr = "">
<CFOUTPUT>
<CFSET variables.datestr = ListAppend(variables.datestr,getdates.activity_date)>
</CFOUTPUT>
<br>#variables.datestr#
</p>
</CFOUTPUT>

nice, simple, and significantly neater than using CFLOOP and managing the groups yourself (to say nothing of how much neater than the equivalent php code would be :p)

ain’t CF elegant?

:cool:

Very nice indeed rudy and very well explained, thank you for that. :tup:

Don’t even mention PHP :slight_smile: If I only look at a simple output I’m already getting dizzy. I’m so happy that I decided to use CF instead of PHP so many tears ago. I’m sure that when it would have be the other way arround, I wouldn’t have any hair left by now :wink:

Thanks to you I can now have a good night sleep :sleeping: