phpmysqldate

subtract days if date range contains dates from a list PHP


I have a calculation that gives the total amount of laboral days between 2 dates. However, if the range of dates contains any date present in a list (holidays), I must subtract each day from holidays from the total amount of days.

It means if in the list of holidays I have the 25-03-2016, and the range of dates go from 21-03-2016 to 25-03-2016, it should count only 4 days, not 5.

At the moment, I have this table of holidays in the DB:

Date
01-01-2016
25-03-2016
27-06-2016
16-07-2016
15-08-2016
19-09-2016
10-10-2016
31-10-2016
01-11-2016
08-12-2016

And the total amount of laboral days comes from this function:

$days=0;
$aux=strtotime($begin_date); #Aux for days
while ($aux <=strtotime($end_date)) { #While the Aux is less than the end, I verify which day of the week is this
    $auxday=date('N',$aux);
    if ($auxday!='6'&&$auxday!='7') { # if is different of 6 or 7, (saturday and sunday) I add 1 to the total amount of days
        $days++;
    }
    $aux=$aux+86400; #I add 1 day to AUX
}

Update

    $sqlF="SELECT * FROM tbl000_feriados";
    $requestF=mysqli_query($connect,$sqlF);
    $holidays=mysqli_fetch_array($requestF);
    foreach($holidays as $arr){
    $holiday_arr[] = strtotime($arr);
    }
    $days=0;
    $aux=strtotime($begin_date);
    while ($aux <=strtotime($end_date)) { 
    if(!in_array($aux,$holiday_arr)){
    $auxday=date('N',$aux);
    if ($auxday!='6'&&$auxday!='7') { 
    $days++;
    }
    }
    $aux=$aux+86400; 
}

It do not subtract the days of the holidays

**** UPADTE 2

The problem is related with the lecture from the table tbl000_feriados, because if I put the data manually into the array

 array(2016-03-25)

for instance, it makes the subtract correctly.

something is going wrong about reading the date from tbl000_feriados

$sqlF="SELECT * FROM tbl000_feriados";
$requestF=mysqli_query($connect,$sqlF);
$holidays=mysqli_fetch_array($requestF);

***** UPDATE 3

For some reason, it make an additional discount when I'm counting days from 23/03 and above 28/03


Solution

  • First, make an array of the dates in holiday list by converting them into strtotime format.

    $days=0;
    $holidays = array('2016-01-01', '2016-03-25');
    foreach($holidays as $arr){
      $holiday_arr[] = strtotime($arr);
    }
    $begin_date = '2016-01-01';
    $end_date = '2017-01-01';
    $aux=strtotime($begin_date); #Aux for days
    while ($aux <=strtotime($end_date)) { 
    

    then compare it with the new array, if this date not exists in holiday list array then, proceed from here

    if(!in_array($aux,$holiday_arr)){
    

    While the Aux is less than the end, I verify wich day of the week is this

        $auxday=date('N',$aux);
        if ($auxday!='6'&&$auxday!='7') { # if is different of 6 or 7, (saturday and sunday) I add 1 to the total amount of days
            $days++;
    }
        }
        $aux=$aux+86400; #I add 1 day to AUX
    }