This seems like a fairly well-known problem. Have you checked any programming books for guidance?
I would suggest two possibilities. If all you need to display is a single day, just retrieve all the records for that day ordered by start time and end time. Then loop over start times by the hour, within each hour, loop over the query and spit out any records that start on or before that hour and end after that hour. If you want a diagram (a la Outlook), you should be able to draw boxes for each session depending on the start time (e.g. full height box for on the hour start times, half height box for an the half hour start times). By looping over the same query for each hour, you get the events in the same order everytime and you can create a diagram. I'm sure there is a better, faster, more sophisticated way to do it, and maybe tomorrow someone who isn't as sleepy as me will share it. If you need to display more than one day's worth of events, you could either loop on the days and do the above query multiple times, or just do a single query grouped by the day and output the query with the group attribute. It might be more trouble than it's worth to write the query with the group by statement, depending on your table structure. Also, if you do it the former way, you end up with less code to maintain, although performance will be faster using one query rather than several. HTH, Rob >Hi folks...I'm a real rookie at CF and have what may be a really simple >problem in retrieving and outputting dynamic data...I need to create an >interactive HTML calendar, where within each date area there needs to appear >several items of information linked to a database.... > > ...so for example within a <td> cell that might represent June 13 we need >to output a string that represents a session time (like "12 noon", >represented in the Sessions database table as "sessionStartTime") as well as >a caption that describes what kind of session it is (like "Gourmet Coffee >Club", "sessionType") and a number which represents the current number of >available spaces still open to be booked (just a number like "7", to be >calculated on the HTML page by subtracting the current value of >"sessionBooked" from "sessionCapacity") and also perhaps a caption that can >be added to the <td> cell to describe the overall current status of that >session (like "open" or "filled" or "closed for Mothers Day", >"sessionCaption")...there are some other fields in the Sessions table as >well, including a sessionDate field and an auto-number sessionID field... > >...so after creating a main query on the calander HTML page asking the db >for all appropriate records and all appropriate fields from the Sessions >table, I guess I thought I could just put some logic like this into a given ><td> cell: "find the record in the main query where sessionDate equals "June >13", then please output the corresponding values of that record for >sessionStartTime, sessionType, and sesssionCaption inside this <td> >cell"...but I now don't see how I can effectively create a dynamic variable >that would to that...I guess I would be asking the data in the main query to >re-arrange itself for me, which perhaps it was not meant to do... > >...then I thought maybe a query of query might be the answer, since it seems >that this construct is made so you CAN re-arrange the data of a main query >already there...but then I thought, this might mean that I would have to >create a new query of query for EACH set of information in the calendar...so >I make a main query, then within each <td> cell I make a QofQ designating >the "where" clause to specify the value of the sessionDate field...then it >seems I could tell CF to output the corresponding values of that particular >record into that cell, by using the "QofQname.sessionCaption" kind of dot >syntax...but would I then have to write forty or fifty or sixty of these >QofQ's for every month, one or more in every date cell of the calendar? >...besides being a pain to write and to keep track of, wouldn't that slow my >web page down to a crawl?... > >...it seems that for so simple a task (?) there should be some more >parsimonious approach...any suggestions?...thanks much! > > >John Petroshius >Prairie Studio >Oakton College ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:206187 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
