调度脚本与PHP/MySQL -帮助逻辑。

I'm looking to develop a small script where users can insert their "schedule." However, I need some help determining the logic of how to create the DB structure and how to input the "time" of the events in the database.

我正在开发一个小脚本，用户可以在其中插入他们的“计划”。但是，我需要一些帮助来确定如何创建DB结构的逻辑以及如何在数据库中输入事件的“时间”。

One thing to note, however, is that when users enter their "schedule", they will not be entering exact dates. Instead, they will be entering 'days of the week.' Somewhat like a 'recurring' appointment.

然而，值得注意的一点是，当用户输入他们的“时间表”时，他们不会输入确切的日期。相反，他们将进入“一周的天数”。有点像“经常性”的约会。

For example, User A could enter the following time schedule:

例如，用户A可以输入以下时间进度表:

MWF - 8:00am - 10:00am MW - 2:00pm - 3:00pm etc...

MWF - 8:00am - 10:00am - 2:00pm - 3:00pm…

As you can see, I'm looking for a way to use generic 'days of the week', not necessarily exact dates.

正如您所看到的，我正在寻找一种方法来使用通用的“一周之日”，而不一定是确切的日期。

With this in mind, what would be the best way to store this in the database, knowing that eventually, I may be "querying" the database to search for available times.

考虑到这一点，在数据库中存储这些数据的最佳方式是什么，因为我知道最终，我可能会“查询”数据库以搜索可用的时间。

Should I enter them in milliseconds or seconds? With "0" being 12:00AM on Sunday?

我应该在毫秒或秒内输入它们吗?“0”在星期天是12点吗?

Any suggestions would be great.

任何建议都很好。

Thanks!

谢谢!

1 个解决方案

#1

First of all, MySQL includes a time data type. I highly recommend you take advantage of it.

首先，MySQL包含时间数据类型。我强烈建议你好好利用它。

Anyway, for your tables (this is for an acadmic scheduling, but the same basic ideas apply):

无论如何，对于您的表(这是一个acadmic调度，但同样的基本思想适用):

DaysOfWeek
----------
DowID int
DayOfWeek varchar(10)

Users
------------------------
UserID int autoincrement
UserName varchar(50)
PassHash varbinary(100)
FirstName varchar(50)
LastName varchar(50)
DOB datetime

Semesters
----------------------------
SemesterID int autoincrement
SemesterName varchar(50)
SemesterNumber int
AcademicYear int
CalendarYear int

Classes
-------------------------
ClassID int autoincrement
ClassName varchar(50)
ClassCode varchar(25)

Schedule
----------------------------
ScheduleID int autoincrement
UserID int
SemesterID int
DowID int
ClassID int
BeginTime time
EndTime time

Now, all you'd need to do to see if somebody's available on a Monday between 1 and 2 is:

现在，你需要做的就是看看周一1点到2点之间是否有人有空:

select
    count(*) as classes
from
    schedule sch
    inner join users u on
        sch.userid = u.userid
    inner join semesters sem on
        sch.semesterid = sem.semesterid
where
    u.username = 'rmcdonald'
    and sem.academicyear = 2009
    and sem.semesternumber = 1
    and sch.dowid = dayofweek($mytime)
    and $mytime between sch.begintime and sch.endtime

Replace the $mytime with whatever time you're checking there.

用您在那里检查的任何时间替换$mytime。

#1