how to check for date/time ranges within record (check for schedule conflicts)
You want to check scheduling conflicts and you have a record like:
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:
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