PHP MySQL remove weekends from query

[LRNPHP]

Hi All,

I'm battling to get this query to work. I have a select form that allows user to select dates. When he submits it it needs to remove weekends from the request.

$DateStart='2013-09-01';
$DateStart='2013-09-05';

$DateSTR=strtotime($DateStart);
$DateSTO=strtotime($DateStop);

for($i=$DateSTR; $i<=$DateSTO; $i++){
    $NewDate=strtotime($i);
    $DateCalc=date('D', $NewDate);  
    $TotalCount++;

 if($DateCalc == 'Sat' || $DateCalc == 'Sun'){
    $WeekendDays++;
    }
}

Regards

[pritaeas]

http://php.net/manual/en/function.date.php

date('w', $NewDate) returns 0 through 6, so you exclude 0 (sunday) and 6 (saturday).

[LRNPHP]

Hi Pritaeas,

Thanks for the quick response. When I run the code it times out with the following message.

Fatal error: Maximum execution time of 30 seconds exceeded.

$WeekendDays=(int)0;
$TotalCount=(int)0;

$DateStart='2014-01-04';
$DateStop='2014-01-08';


$DateSTR=strtotime($DateStart);
$DateSTO=strtotime($DateStop);

for($i=$DateSTR; $i<=$DateSTO; $i++){
    $NewDate=strtotime($i);
    $DateCalc=date('w', $NewDate);  
    $TotalCount++;

if($DateCalc == 'Sat' || $DateCalc == 'Sun'){
    $WeekendDays++;
    }
}

What am I doing wrong?

Regards

[cereal]

It can be done also at query level by using dayofweek(). Differently from PHP the range is 1-7: 1 for sunday, 7 for saturday, for example:

 select * from images where dayofweek(created_at) NOT IN(1,7);

Docs: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek

[LRNPHP]

Hi Cereal,

I have 2 tables DateStart & DateStop where I would like to check if there's any weekends in.

$DateStart='2014-01-04';
$DateStop='2014-01-08';

select * from table where DateStart between '2014-01-04' and '2014-01-08' where dayofweek(DateStart) NOT IN(1,7)

Is this correct?

regards

[LRNPHP]

When I run this query.

SELECT DateStart, DateStop, dayofweek( DateStart ) AS CurrentDay
FROM table
WHERE DateStart
BETWEEN '2014-01-21'
AND '2014-01-29'
AND dayofweek( DateStart ) NOT
IN ( 1, 7 );

I get.

DateStart   DateStop    CurrentDay
2014-01-24  2014-01-27         6

Regards

[LRNPHP]

Hi Pritaeas,

The query.

$WeekendDays=(int)0;
$TotalCount=(int)0;

$DateStart='2014-01-04';
$DateStop='2014-01-08';


$DateSTR=strtotime($DateStart);
$DateSTO=strtotime($DateStop);

for($i=$DateSTR; $i<=$DateSTO; $i++){
    $NewDate=strtotime($i);
    $DateCalc=date('w', $NewDate);  
    $TotalCount++;

if($DateCalc == '0' || $DateCalc == '6'){
    $WeekendDays++;
    }
}

[LRNPHP]

$WeekendDays=(int)0;
$TotalCount=(int)0;

$DateStart='2014-01-04';
$DateStop='2014-02-04';


$DateSTR=strtotime($DateStart);
$DateSTO=strtotime($DateStop);

for($i=$DateStart; $i<=$DateStop; $i++){
    $NewDate=strtotime($i);
    $DateCalc=date('w', $NewDate);  
    $TotalCount++;

if($DateCalc == '0' || $DateCalc == '6'){
    $WeekendDays++;
    }
}
    echo $WeekendDays.'<br>';

[LRNPHP]

What I need in the DB

$DateStart='2014-01-31';
$DateStop='2014-02-12';

$Totaldays=13;
$Weekdays=9;
$Weekend=4;
insert into dbtable(date_ID, Date_Start, DateStop, TotalDays)values('', '$DateStart', '$DateStop', '$Weekdays');

[LRNPHP]

Hi Everyone,

I found a solution, it's not the best but it works. Thanks for everyone who helped assistance.

<?php

$WeekendDays=(int)0;
$TotalCount=(int)0;

$DSEM=explode('-', $DateStart);
$DSM=$DSEM[1];
$DSD=$DSEM[2];

$DSDMD=$DSM.'-'.$DSD;

$DEEM=explode('-', $DateStop);
$DEY=$DEEM[0];
$DEM=$DEEM[1];
$DED=$DEEM[2];

$DEDMD=$DEM.'-'.$DED;


$DateSCOM=$DSDMD;
$DateECOM=$DEDMD;

if($DSM == $DEM){
    for($i=$DateStart; $i<=$DateStop; $i++){ 
    $NewDate=strtotime($i);
    $DateCalc=date('w', $NewDate);  
    $TotalCount++;

if($DateCalc == '0' || $DateCalc == '6'){
    $WeekendDays++;
    }
}
}else{
        $monthS=date("m", strtotime($DateStart));
        $yearS=date("Y", strtotime($DateStart));

        $MonthDays=cal_days_in_month(0, $monthS, $yearS);
        $DateEnd=$yearS.'-'.$monthS.'-'.$MonthDays;

        for($i=$DateStart; $i<=$DateEnd; $i++){  
            $NewDate=strtotime($i);
            $DateCalcN=date('w', $NewDate);  
            $TotalCount++;

if($DateCalcN == '0' || $DateCalcN == '6'){
    $WeekendDays++;
    }
}           
        $month=date("m", strtotime($DateStop));
        $year=date("Y", strtotime($DateStop));
        $DateStartNew=mktime(0,0,0,$month,1,$year);

        $DateStartNew=date("d", $DateStartNew);
        $DateStartNew=$DEY.'-'.$DEM.'-'.$DateStartNew;


        for($i=$DateStartNew; $i<=$DateStop; $i++){
            $NewDate=strtotime($i);
            $DateCalc=date('w', $NewDate);  
            $TotalCount++;

if($DateCalc == '0' || $DateCalc == '6'){
    $WeekendDays++;
    }
}       
}
?>

Cheers