CODEWITHSUNDEEP
phpmysqli
Ingus
Ingus

Reputation: 1044

count days in month

I have code that counts working days in month . If in month there is 1 period everything is counted good.

But if in month (this case August) is more than one period it doesn't work

Dates example <br>
2016-08-02 - 2016-08-04 (3 working days)<br>
then <br>
2016-08-08 - 2016-08-10 (3 working days)<br>
and 2016-08-24 - 2016-09-02 (6 working days)<br>

So it would be 12 working days in August

Here is my mysqli query:

$getaways =  mysqli_query($conn, "SELECT * FROM employeesAbsence 
                                  WHERE workerID='".$row['worker']."' 
                                  AND fromDate LIKE  '%2016-08%' 
                                  AND toDate LIKE  '%2016-08%'); 
$aways_row = mysqli_fetch_array($getaways);
$fDate = $aways_row['fromDate'];
$tDate = $aways_row['toDate'];

and here is php:

function get_days($start, $end)
{  
    $begin = new DateTime( $start );
    $end   = new DateTime( $end );
    //$end   = $end->modify( '+1 day' ); //add one day so as to include the end date of our range

    $total_days = 0;
    //this will calculate total days from monday to friday in above date range
    for( $i = $begin; $i <= $end; $i->modify( '+1 day' ) )
    {
        // Check that the date is between Monday and Friday and only in August
        if( ( $i->format( 'N' ) >= 1 && $i->format( 'N') <= 5 ) && $i->format( 'm' ) == '08' )
        {
            $total_days++;
        }
    }
    return $total_days;
}

$total = 0;
$total += get_days( $fDate, $tDate);

This code returns 3 as the number of working days

What am I missing or doing wrong? Thanks.

Upvotes: 0

Views: 122

Answers (1)

RiggsFolly
RiggsFolly

Reputation: 94642

Ok I think I get the issue now, you are getting more than one row returned from the query but you are only using the first row in your calculation.

You need to loop over the 3 rows returned by the query.

function get_days( $start, $end )
{  
    $begin = new DateTime( $start );
    $end   = new DateTime( $end );

    $total_days = 0;
    //this will calculate total days from monday to friday in above date range
    for( $i = $begin; $i <= $end; $i->modify( '+1 day' ) ) {
        // Check that the date is between Monday and Friday and only in August
        if( ( $i->format( 'N' ) >= 1 && $i->format( 'N') <= 5 ) && $i->format( 'm' ) == '08' ) {
            $total_days++;
        }
    }
    return $total_days;
}

$getaways =  mysqli_query($conn, "SELECT * FROM employeesAbsence 
                                   WHERE workerID='".$row['worker']."' 
                                   AND MONTH(fromDate) = 8 
                                   AND MONTH(toDate) = 8"); 

if ( ! $getaways ) {
    echo mysqli_error($getaways);
    exit;
}

// just check how many row you get back
echo 'Query generated ' . mysqli_num_rows($getaways) . ' rows';

$total = 0;
while ($row = mysqli_fetch_array($getaways) ){
    $total += get_days($row['fromDate'], $row['toDate']);
}
echo $total;

To get the results you are suggestiong that you want, try changing the query to

$getaways =  mysqli_query($conn, "SELECT * FROM employeesAbsence 
                                   WHERE workerID='".$row['worker']."' 
                                   AND MONTH(fromDate) = 8");

This will then return you all rows where the holiday started in August regardless of when the holiday finished.

Upvotes: 1

Related Questions