Posted by Notgiven on 01/20/06 22:18

You want to check scheduling conflicts and you have a record like:

appointments(table):
apptID
beginningDate
endingDate
beginningTime
endingTime

It's easy enough to check if a time is within that record. Say you want to
check if 8:00am to 10:00am is available, you would use this:

SELECT apptID
FROM appointments
WHERE (beginningDate = '2006-01-19' OR endingDate = '2006-01-19')
AND ('08:00:00' BETWEEN beginningTime AND endingTime
OR '10:00:00' BETWEEN beginningTime AND endingTime)

BUT, what if you have an all-day appointment (8:00am to 5:00pm) and there
exists an appointment already scheduled from 10:00am to 11:00am. The above
query would not find it.

Another question is what if the appointment is more than two days. Say, it's
from Monday - Wednesday from 8am to 5pm. The above query would not
successfully catch it if you wanted to schedule an appointment on Tuesday.
(I might be able to generate a date range using PHP, don't know if that's
the best way)

Any ideas? Thank you very much for any help!

PS: I am using MySQL 3.23.55 and PHP4.x

• Next in forum: Re: why would a large not upload, when a small does, when it is still within the POST limit?
• Prev in forum: Help: questions about query structure in imap_search
• Thread view: how to check for date/time ranges within record (check for schedule conflicts)

[Reply to this message]