Hi Thomas, > SELECT * from $DB_TBLName WHERE > (Trim(WorkerEmail)='$userReplacementEmail') > AND AND OR OR OR OR OR
This is all a bit complicated, and a simple boy like me gets lost too easily. KISS principle: Keep it simple... You have two employees: A, so named because he Already has Annual leave Approved, and B, so named because he is Begging to Be allowed to Break out. Let's imagine we're playing with a wall-chart/wall-planner to assess requests prior to granting approval. So we mark off which days A will be away and then view which days B would like to go against existing requests. Month A Month B Month C Month etc A <-------------------> B <........................> Assuming the email/fonts haven't mucked-up my sketch, you can see that B's request should be denied, because A has already booked the time-out. Now let's use the diagram to build an algorithm. How many ways are there that A and B could overlap? (hint: four) Not overlap? (hint: two) Which is the easiest to implement in code? Now you can ask, which language should I code in? Answer: stay as close to the source as possible, ie use SQL (or in this case pseudo-SQL(!)). SELECT a AS Colleague, CONCAT( astart - afinish ) AS PermissionDenied FROM tbl WHERE bstart < afinish OR bfinish > astart; Before this will work, you will need to add another WHERE clause to establish who is the employee's "replacement". The above lists a reason for rejection - you can reverse the logic, at your peril. Trust this assists, =dn PS The complicating question I wanted to ask from the word "go" is: what if one person can have more than one other employee act as his/her "replacement"? > 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php