[RESOLVED] Date check question

[FraXTC]

Hey everyone,

My question is quite general so searching for it proved to hard, so I'll just ask it:

I have a calander, where you can plan actions that return on a weekly base, on a specific time and room. But also, you can plan a specific action, that only occurs that one time. When entering a new weekly action, I want to check if there are any specific actions that occur only once on that particular day, time and room.

The data concerning the weekly and specific (one time) actions are stored in seperate tables:

The "timestamp" of the specific actions are literally dates.
The timestamp of the weekly actions is a day, combined with a start date (for the first time the weekly action is planned) and an end date (the last time the weekly action will take place).

So if I would like to check if a new weekly action occurs at the same time as a specific (one time) action, I have to compare the days (Monday) with dates (21-11-2005)

The trouble is, that I want to be able to check ALL dates in one go. So for instance: when a new weekly action is made for a monday 12:00 in room 2 with startdate 14-11-2005, I want a way to easily send a query to mySQL so it would spot that the new weekly action on monday is in conflict with the one time action on 21-11-2005 , but also the one time action on 08-11-2010 or 05-05-2064 or any other Monday from the year 2000 to the year 2100...

It's probably gonna take more than one simple query, but I don't even have a clue of how to cope with this problem... Could anyone of you help me out?

[Roger_Ramjet]

Hmmm, interesting 🆒

Assuming single action table called 'tbl' and timestamp called 'booking', and assuming input vars $room, $startdate, $enddate, $weekday, $time (using 24 hour clock)

SELECT * FROM tbl WHERE
room = '$room'                           // match room records first
AND booking >= $startdate AND booking <= $enddate   // check date range match
AND dayname(booking) = '$weekday'     // then match day of week
AND date_format(booking, %H) = '$time'   // then match time, assumes 0-23 hour

That should find any and all matches for room, weekday and time that fall within the date range.
You could tweak the order of conditions to find the quickest, don't know how much diff there would be.

[FraXTC]

At first glance I think this should do the trick, I'll try to use this and I'll "report back" if I have a question 🙂

thankx Roger!

[FraXTC]

Yep, that did the trick, thanx 🙂 I never heard of dayname before, but I find it very handy 🙂

Cheers! 🆒