would you use all joins if you were building the query or do sub queries? I'm still running through in my head how I should write this thing.
Ben On Fri, Jun 5, 2009 at 9:51 AM, Azadi Saryev <[email protected]> wrote: > > 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:323206 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
