Add Days from Input Type Date and Input Type Duration PHP Excluding Weekends + MYSQL National Holiday

Question

I need to add days (input type number + input type date) but the result must be an array so I can INSERT one after another into the Database.

Here's the code (After HTML Form submitted):

<?php

$start_date = '2017-12-22';
$duration = '3';
$d = new DateTime($start_date);
$t = $d->getTimestamp();

// loop for X days
for($i=0; $i <= $duration; $i++){

    // add 1 day to timestamp-
    $addDay = 86400;

    // get what day it is next day
    $nextDay = date('w', ($t + $addDay));
    
    // if it's Saturday or Sunday get $i-1
    if($nextDay === 6 || $nextDay === 7) {
        $i --;
    }

    // modify timestamp, add 1 day
    $t = $t + $addDay;
    
    $d->setTimestamp($t);
    $day_off = $d->format( 'Y-m-d' ). "<br />";
    
    echo $day_off;
    
    $query = "INSERT SQL";
    
}

?>

From echo $day_off result I get:

2017-12-23
2017-12-24
2017-12-25
2017-12-26

Instead of 23, 24, 25, 26. I need to get the result below:

2017-12-22 2017-12-25 2017-12-26 2017-12-27

22 is the input date, start from 25 because 23 and 24 are Sat and Sun and weekends need to be excluded.

How can I achieve this result? I've been searching on the net but unfortunately, I couldn't find what I needed.

@C. Geek answer made it to works, but I have a more complex question here, since my account are not eligible to ask more question so I'll ask here.

So here's what I've tried so far (with @C. Geek answer) :

<?php

// loop for X days
for($i=0; $i < $duration; $i++){
    
    
    $d = strtotime("$start_date +$i weekdays");
    $t = strftime("%Y-%m-%d",$d);
    
    $day_off[] = $t;
    
    foreach($day_off as $dayoff) {
        
        $data_holiday = mysqli_fetch_array(mysqli_query($con, "SELECT * FROM `holiday_master_data` WHERE `date` = '$dayoff' "));
        
    }
    
    $holiday[] = $data_holiday['date'];
    $date = array_diff($day_off, $holiday);
    $dayoff_ = $holiday;
    
?>

Start date : 2017-12-29 Duration : 5 days

From print_r($day_off); I'm getting this result :

Array ( [0] => 2017-12-29 ) Array ( [0] => 2017-12-29 [1] => 2018-01-01 ) Array ( [0] => 2017-12-29 [1] => 2018-01-01 [2] => 2018-01-02 )

And from print_r($holiday); I'm getting this result :

Array ( [0] => ) Array ( [0] => [1] => 2018-01-01 ) Array ( [0] => [1] => 2018-01-01 [2] => ) Array ( [0] => [1] => 2018-01-01 [2] => [3] => ) Array ( [0] => [1] => 2018-01-01 [2] => [3] => [4] => )

The national date fetched from database is 2018-01-01 with 5 looping result, the final date result I need to make are 29 Dec, 02 Jan 03 Jan and 04 Jan, 05 Jan.

Any help will be much appreciated. Thanks.

Answer 1

https://stackoverflow.com/a/4261223/6288442

If you are limiting to weekdays use the string weekdays.

echo date ( 'Y-m-j' , strtotime ( '3 weekdays' ) );

This should jump you ahead by 3 weekdays, so if it is Thursday it will add the additional weekend time.

Source: http://www.php.net/manual/en/datetime.formats.relative.php

As for formatting: http://php.net/manual/en/function.strftime.php

 string strftime ( string $format [, int $timestamp = time() ] )

If you need more help with writing the code than these, please do tell in a comment

Here is my full answer:

$start_date = '2017-12-22';
$duration = 3;
$arr=null;
for($i=0; $i <= $duration; $i++){
    $d = strtotime("$start_date +$i weekdays");
    $t = strftime("%Y-%m-%d",$d);
    $arr[]=$t;
}

Get the holidays before the looping, then in the loop, check if date is in_array before adding it to $arr. e.g.

$start_date = '2017-12-22';  
$data_holiday = mysqli_fetch_array(mysqli_query($con, "SELECT * FROM `holiday_master_data` WHERE YEAR(`date`) BETWEEN YEAR('$start_date') AND YEAR('$start_date')+1 "));
$holidays = 
$duration = 3;
$arr=null;
for($i=0; $i <= $duration; $i++){
    $d = strtotime("$start_date +$i weekdays");
    $t = strftime("%Y-%m-%d",$d);
    if(!in_array($t,$data_holiday))
        $arr[]=$t;
}

Answer 2

FINALLY!! After several hours I fixed everything. Here's the code how I manage to skip (Sun and Monday) and also Skip the Holiday's fetched from the database (based on @C.Geek answers + several tweaking):

<?php

include 'conn.php';
$start_date = mysqli_real_escape_string($con, $_POST['start_date']);
$duration = mysqli_real_escape_string($con, $_POST['duration']);

// loop for X days
for($i=0; $i <= $duration; $i++){
    
    $d = strtotime("$start_date +$i weekdays");
    $t = explode(", ", strftime("%Y-%m-%d", $d));
    
    foreach ($t as $date) {
        
        $to_encode = array("date" => $date);
        $date_where = $to_encode['date'];
        
        $data_holiday = mysqli_fetch_array(mysqli_query($con, "SELECT `date` AS '0' FROM `holiday_master_data` WHERE DATE(`date`) BETWEEN DATE('$date_where') AND DATE('$date_where') + 1 GROUP BY `id` "));
        
        $encode_holiday = array("date" => $data_holiday[0]);
        
        break;
        
    }
    
    $holiday = array_unique($encode_holiday);
    $dayoff = array_diff($t, $holiday);
    
    foreach($dayoff as $date) {
        
        $query = mysqli_query($con, "INSERT INTO ");
    
        if ($query) {
            echo "<script>alert('Absence Saved'); window.location ='document.php' </script>";
        } else {
            echo "<script>alert('Gagal'); window.location ='document.php' </script>";
        }
    }
    
    
}

?>

Hope this helps anyone seeking the same problem I had.

Cheers.