Checking for Overlapping Dates
| From: | Thomas dot Churm at comstor dot de | Date: | Thu, 05 Dec 2002 10:01:00 +0000 |
| Subject: | Checking for Overlapping Dates | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-127123@lists.php.net to get a copy of this message | ||
Hi,
Background:
================
I've created a vacation-request application for our company's intranet.
When an employee requests a vacation, he has to list the name of another
employee who will 'fill in' for him while he is gone.
Before a vacation request can be saved in MySQL, I need to check to make
sure that the person listed as the employee's replacement during this time
has not already requested a vacation during the same time period...to
check to be sure that the two vacation periods do not overlap.
Date Ranges:
================
I have fields saved in MySQL called unixStartDate & unixEndDate that are
unix timestamps for the first day of the employee's vacation and the last
day of the emplosyee's vacation.
I have tried to do a check using purely SQL, but this doesn't account for
all possibilities of overlapping dates:
(unixStartDate is the unix timestamp for the replacement, whereas the PHP
var $unixStartDate is the unix timestamp for the employee who wants to
post a new vacation request)
$checkSQL = "SELECT * from $DB_TBLName WHERE
(Trim(WorkerEmail)='$userReplacementEmail')
AND (Status < 40)
AND
(
(
($unixStartDate = unixStartDate)
)
OR
(
($unixEndDate = unixEndDate)
)
OR
(
($unixStartDate = unixEndDate)
)
OR
(
($unixEndDate = unixStartDate)
)
OR
(
(unixStartDate < $unixStartDate) && (unixEndDate > $unixEndDate)
)
OR
(
(unixStartDate > $unixStartDate) && (unixEndDate < $unixEndDate)
)
)
";
So I think what I instead need to do is use PHP code instead of SQL to
check for overlapping dates in the 2 date ranges I have.
My two ranges would be like this:
Replacement's Date Range:
================
$unixStartDateReplacementVacation
...to...
$unixEndDateReplacementVacation
Employee's Date Range:
================
$unixStartDateEmployeeVacation
...to..
$unixEndDateEmployeeVacation
================
...so i need to check that none of the dates occuring in the first date
range listed above appear in the second date range.
unfortunately, i have no basic idea of how i should go about doing
this...should i use arrays of dates, for-loops, or what?
thanks a whole lot in advance,
Tom