开发者

How to create an availability script using PHP and MySQL based on schedule entries?

开发者 https://www.devze.com 2023-03-18 16:43 出处:网络
I am trying to develop a tool for a client that returns all available users for a given day and time of the week.For example, the user will enter their current weekly schedule, and the database will s

I am trying to develop a tool for a client that returns all available users for a given day and time of the week. For example, the user will enter their current weekly schedule, and the database will store that schedule. Each row of that table will contain the Username, Day of Week, Start Time of unavailability, and End Time of unavailability. If the client were to input a day, start time, and end time, I want the script to return all users who are available for that given time. Do you have any ideas how to approach thi开发者_如何学编程s problem? Specifically, I'm looking for the most efficient way to store the information and process it. Any help would be greatly appreciated. Here's an example of a possible table structure:

UserName     DayOfWeek     StartTime     EndTime   //Times when unavailable
jdoe         Mon           09:30:00      10:30:00
jdoe         Mon           11:30:00      12:45:00
jdoe         Tue           14:50:00      16:20:00
hsmith       Wed           16:20:00      17:55:00           


Store the Start and End times as dates e.g. 2011-01-01 09:00:00 then for availability you could do something like

SELECT *
  FROM userTable
  WHERE userName NOT IN (SELECT userName
                           FROM userTable
                           WHERE #{date to check} BETWEEN startTime AND endTime)

I've not tried this out and there is some normalisation to be done but should be a starting point

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号