that's a tricky layout... but only from css part! on the cf part all you need is one query to pull all data (with 2 extra columns aliased to store extracted YEAR** and MONTH from event's dates) and the nested <cfoutput>s with GROUP attributes: first by year column, then month column, then date column, then finally a cfoutput without any grouping for the times and checkboxes.
** you will need a year as separate column for cases when an event spans over Dec 31 Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ On 05/06/2009 20:24, Ben Densmore wrote: > Hi Azadi, > Here is an image of what the output should ultimately be. > > http://wedapstudios.com/table.png > > Right now my first query to get the Grouped months looks like this: > Select mtsch_DateID, date_format(mtsch_DateVal, '%M') as Month, count(*) > as Amount > From mtsch_dates > Where mtsch_PollFK = <cfqueryparam cfsqltype="cf_sql_integer" > value="#arguments.pollID#" /> > group by date_format(mtsch_DateVal, '%M') > Order by mtsch_DateVal > > I'm then passing in the DateID to another function to grab the list of days: > > Select mtsch_DateVal as Days,mtsch_DateID > From mtsch_dates > Where mtsch_PollFK = <cfqueryparam cfsqltype="cf_sql_integer" > value="#arguments.pollID#" /> > And date_format(mtsch_DateVal, '%M') = <cfqueryparam > cfsqltype="cf_sql_varchar" value=#arguments.monthvalue" /> > > I know I can probably get this all in one query but was having a hard time > getting it completely right. I since deleted the query I was using to get > the grouped months and then the days. I'll try and tackle doing it all in > one query again. > > Thanks, > Ben > > On Fri, Jun 5, 2009 at 8:26 AM, Azadi Saryev <[email protected]> wrote: > >> Ben, in my Thunderbird you desired query output looks all warped - i >> can't make out exactly how you need it diplayed... can you try and post >> it formatted with tabs, or maybe just describe it in words? >> >> >> Azadi Saryev >> Sabai-dee.com >> http://www.sabai-dee.com/ >> >> >> On 05/06/2009 12:34, Ben Densmore wrote: >>> I've been working on an event scheduling application for the past 2 >> weeks. >>> I'm pretty close to wrapping it up but have now run into an issue on my >> last >>> part of the display. I'm thinking my data model might be the issue and >> would >>> like to get some ideas from people on how I might be able to fix it. >>> >>> The way this thing works is: >>> Step 1. user Creates a Title and description for their event >>> Step 2. the user can select as many dates as they want >>> step 3. the user enters in multiple times for each date they selected >>> >>> The structure I'm using for storing the data looks like: >>> >>> Table 1 >>> PollID|UserID|Title|Description >>> >>> Table 2 >>> DateID|PollFK|DateValue >>> >>> Table 3 >>> DateFK|PollFK|TimeValue|Order >>> >>> This structure made the most sense to me only because they can add any >>> number of dates that they want so if they added 15 dates I create a new >> row >>> for each date and the ID for that date will then relate to the times they >>> enter in step 3. This is what one event might look like in these 3 tables >>> >>> Table 1 >>> PollID | UserID | Title | Description >>> 1 25 My Title My Description >>> >>> Table 2 >>> DateID | PollFK | DateValue >>> 1 1 2009-06-25 >>> 2 1 2009-06-26 >>> 3 1 2009-07-27 >>> >>> Table 3 >>> DateFK | PollFK | TimeValue | Order >>> 1 1 9:00 AM 1 >>> 1 1 10:00 AM 2 >>> 1 1 12:00 PM 3 >>> 2 1 11:00 AM 1 >>> 2 1 12:00 PM 2 >>> 3 1 6:30 PM 1 >>> >>> In My last display page I need to show a table similar to this: >>> >>> >>> June >>> July >>> Wed 25 Thur 26 >>> Wed 01 >>> 9:00 AM 10:00 AM 12:00 PM 11:00 AM 12:00 PM 6:30 PM >>> >>> I'm just really struggling to get this layout done with multiple queries. >>> I'm doing one query to group the dates by month then going back and >> getting >>> the days that were entered in for that month which I can do fine but I >> can't >>> get the display to show up as I showed above. >>> >>> So I guess my 2 questions are: >>> >>> 1. Does anyone see a more efficient way to store the dates and times, >>> keeping in mind that a user can add as many dates as they want, so >> creating >>> x amount of columns doesn't really seem feasible. >>> >>> 2. What would be the best way to build my display page? >>> >>> Thanks, >>> Ben >>> >>> >>> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323200 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
