如何显示每天的事件? [英] How can I display each days events?
问题描述
我有一个活动页面，我需要在其中显示每天的活动.我已经做到了这一点，所以我正在取得进展.
I have an events page where I need to display the events for each day. I've gotten it to this point, so I'm making progress.
数据库有3个表:fairdays、eventtypes、events
The database has 3 tables: fairdays, eventtypes, events
fairdays:id、fairdaydate(日期时间)、daycolor、描述
fairdays: id, fairdaydate (datetime), daycolor, description
eventtypes: id, eventtype <--此表是为添加事件表单"中的输入选择
eventtypes: id, eventtype <-- This table is for the input select in the "add event form"
事件:id、事件名称、事件类型、事件日(日期时间)、事件时间(日期时间)
events: id, eventname, eventtype, eventday (datetime), eventtime (datetime)
我的意图是显示带有描述的日期，然后在那个下面是事件类型，然后在每个下面是对应的事件.
My intent is to display the day with the description, then under that is the event types, then under each of those is the corresponding events.
我还没有想出如何显示事件类型子标题，然后是每个事件下的单个事件，但这是我到目前为止的代码.任何帮助将不胜感激.
I haven't worked out how to display the event type sub heading, then the individual events under each of those, but here's my code so far. Any help would be hugely appreciated.
<cfquery datasource="fairscheduledb" name="getfairdays">
select * from fairdays
</cfquery>
<cfquery datasource="fairscheduledb" name="getfairevents">
select * from events ev
inner join fairdays fd on fd.fairdaydate = ev.eventday
where 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 what the list of days and events should look 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 at Gate<br/>5:00 - 6:00pm - Free Admission & 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 the Robot</div>
<div class="scheduleitem">7:30pm - Scotts Magic Show</div>
<div class="scheduleitem">8:30pm - Rock-It the 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>
推荐答案
(评论太长了……)
为了扩展 Dan 的回答，他建议使用 JOIN 和
To expand on Dan's answer, he is suggesting a more efficient way of producing that output by using JOIN's and cfoutput's group feature (emphasis is mine):
...在数据排序时消除相邻的重复行.如果你使用检索在一个或多个查询列上排序的记录集.为了例如，如果记录集在 cfquery 中按Customer_ID"排序标签，您可以在Customer_ID"上对输出进行分组.
... Eliminates adjacent duplicate rows when data is sorted. Use if you retrieved a record set ordered on one or more a query columns. For example, if a record set is ordered on "Customer_ID" in the cfquery tag, you can group the output on "Customer_ID."
对于您的 JOIN，您需要包含所有三个表以获取您需要的所有列.我现在无法对其进行测试，但可以进行一些类似的测试.(请注意，结果的排序方式与您希望显示的方式相同，即按事件日期、类型和时间)
For your JOIN's you will need to include all three tables to grab all of the columns you need. I cannot test it right now, but something along these lines. (Notice the results are sorted the same way you wish to display them ie by event date, type and time)
SELECT ev.eventDay, t.EventType, ev.EventTime
FROM fairdays fd
INNER JOIN events ev ON ev.eventDay = fd.fairdaydate
INNER JOIN eventType t ON t.ID = ev.EventType
ORDER BY ev.eventDay, t.EventType, e.EventTime
获得排序结果后，使用组"生成所需的结果.请务必按与 sql 查询相同的列以相同的顺序分组.否则，它将无法正常工作.
Once you have the sorted results, use "group" generate the desired results. Be sure to group by the same columns, in the same order, as the sql query. Otherwise, it will not work correctly.
<cfoutput query="yourQuery" group="EventDay">
<!--- display event dates --->
#EventDay# <hr/>
<!--- event types for current date --->
<cfoutput group="EventType">
#EventType#<br/>
<!--- individual events --->
<cfoutput>
#EventTime# <br/>
</cfoutput>
</cfoutput>
</cfoutput>
评论更新:
正如评论中所讨论的，如果你想检索所有的 Fairdays(即使是没有匹配事件的)使用 外部联接而不是内部联接.
As discussed in the comments, if you want to retrieve all fairdays (even ones without a matching event) use outer joins instead of inner joins.
SELECT fd.fairDayDate, t.ID, ev.EventType, ev.EventTime
FROM fairdays fd
INNER JOIN events ev ON ev.eventDay = fd.fairDayDate
INNER JOIN eventType t ON t.ID = ev.EventType
ORDER BY fd.fairDayDate, t.ID, e.EventTime
同样，由于 cfoutput "group" 功能需要排序的查询数据才能正常工作，如果您更改 ORDER BY 子句，请务必更新 "group" 列以匹配您的 ORDER BY 子句.即先按fairDayDate分组，然后按事件ID:
Again, since the cfoutput "group" feature requires sorted query data to work properly, if you change the ORDER BY clause, be sure to update the "group" columns to match your ORDER BY clause. ie Group by fairDayDate first, then event ID:
<cfoutput query="yourQuery" group="fairDayDate">
<!--- display event dates --->
#fairDayDate# <hr/>
<!--- event types for current date --->
<cfoutput group="ID">
#EventType#<br/>
<!--- individual events --->
<cfoutput>
#EventTime# <br/>
</cfoutput>
</cfoutput>
</cfoutput>
这篇关于如何显示每天的事件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
