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:323199 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
