coldfusion - How can I display each days events? -

i have events page need display events each day. i've gotten point, i'm making progress.

the database has 3 tables: fairdays, eventtypes, events

• fairdays: id, fairdaydate (datetime), daycolor, description

• eventtypes: id, eventtype <-- table input select in "add event form"

• events: id, eventname, eventtype, eventday (datetime), eventtime (datetime)

my intent display day description, under event types, under each of corresponding events.

i haven't worked out how display event type sub heading, individual events under each of those, here's code far. hugely appreciated.

<cfquery datasource="fairscheduledb" name="getfairdays">     select * fairdays </cfquery>  <cfquery datasource="fairscheduledb" name="getfairevents">     select * events ev     inner join fairdays fd on fd.fairdaydate = ev.eventday     ev.eventday = fd.fairdaydate </cfquery>  <cfloop query="getfairdays">  <cfoutput>     <div class="schedulebox">         <div class="schedulehead" style="color: ###getfairdays.daycolor#;">#dateformat(getfairdays.fairdaydate,"dddd, mmmm dd")#</div>         <div class="schedulesubhead" style="color: ##ffffff; background: ###getfairdays.daycolor#;">#getfairdays.description#</div>         <cfoutput query="getfairevents">             <div class="scheduleitem" style="float: left; width: 75px; text-align: right;">#lcase(timeformat(getfairevents.eventtime,"h:mmtt"))#</div>             <div class="scheduleitem" style="float: left; width: 550px;">#getfairevents.eventname#</div><br/>         </cfoutput>     </div> </cfoutput>  </cfloop> 

here's list of days , events should like:

<div class="schedulebox">      <div class="schedulehead" style="color: #4cc417;">friday, february 22</div>     <div class="schedulesubhead" style="color: #ffffff; background: #4cc417;">opening ceremony 4:30pm @ gate<br/>5:00 - 6:00pm - free admission &amp; free rides</div>     <div class="scheduleitemtitle" style="color: #4cc417;">strolling acts</div>         <div class="scheduleitem">5:30pm - scotts magic show</div>         <div class="scheduleitem">6:30pm - rock-it robot</div>         <div class="scheduleitem">7:30pm - scotts magic show</div>         <div class="scheduleitem">8:30pm - rock-it robot</div>     <div class="scheduleitemtitle" style="color: #4cc417;">acts</div>         <div class="scheduleitem">5:30pm - sea lion show</div>         <div class="scheduleitem">6:00pm - alligator wrestling</div>         <div class="scheduleitem">6:30pm - petting zoo presentation </div>         <div class="scheduleitem">8:00pm - alligator wrestling</div>         <div class="scheduleitem">8:30pm - petting zoo presentation </div>         <div class="scheduleitem">9:00pm - sea lion show</div>         <div class="scheduleitemtitle" style="color: #4cc417;">stage acts</div>         <div class="scheduleitem">7:00pm - youth royalty</div>     <div class="scheduleitemtitle" style="color: #4cc417;">livestock program</div>         <div class="scheduleitem">6:00pm - beef breeding screening</div>         <div class="scheduleitem">7:00pm - horse judging competition</div>  </div> 

(this long comments ...)

to expand on dan's answer, suggesting more efficient way of producing output using join's , cfoutput's group feature (emphasis mine):

... eliminates adjacent duplicate rows when data sorted. use if retrieved record set ordered on 1 or more query columns. example, if record set ordered on "customer_id" in cfquery tag, can group output on "customer_id."

for join's need include 3 tables grab of columns need. cannot test right now, along these lines. (notice results sorted same way wish display them ie event date, type , time)

select  ev.eventday, t.eventtype, ev.eventtime    fairdays fd            inner join events ev on ev.eventday = fd.fairdaydate           inner join eventtype t on t.id = ev.eventtype  order ev.eventday, t.eventtype, e.eventtime 

once have sorted results, use "group" generate desired results. sure group same columns, in same order, sql query. otherwise, not work correctly.

<cfoutput query="yourquery" group="eventday">         <!--- display event dates --->           #eventday# <hr/>         <!--- event types current date --->     <cfoutput group="eventtype">         #eventtype#<br/>         <!--- individual events --->         <cfoutput>             #eventtime# <br/>         </cfoutput>     </cfoutput>  </cfoutput> 

update comments:

as discussed in comments, if want retrieve fairdays (even ones without matching event) use outer joins instead of inner joins.

select  fd.fairdaydate, t.id, ev.eventtype, ev.eventtime    fairdays fd            inner join events ev on ev.eventday = fd.fairdaydate           inner join eventtype t on t.id = ev.eventtype  order fd.fairdaydate, t.id, e.eventtime 

again, since cfoutput "group" feature requires sorted query data work properly, if change order clause, sure update "group" columns match order by clause. ie group fairdaydate first, event id:

<cfoutput query="yourquery" group="fairdaydate">         <!--- display event dates --->           #fairdaydate# <hr/>         <!--- event types current date --->     <cfoutput group="id">         #eventtype#<br/>         <!--- individual events --->         <cfoutput>             #eventtime# <br/>         </cfoutput>     </cfoutput>  </cfoutput>