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.
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
AND (Status < 40)
($unixStartDate = unixStartDate)
($unixEndDate = unixEndDate)
($unixStartDate = unixEndDate)
($unixEndDate = unixStartDate)
(unixStartDate < $unixStartDate) && (unixEndDate > $unixEndDate)
(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:
Employee's Date Range:
...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,