calculate difference between 2 days without holidays

[prince198]

HI evrybody
i have probleme in script and i need your help
i have script calculate total of number between 2 days without sunday
my sql table is like this :


start_day  \     end_day
2007-01-01\   2008-01-01
2008-01-01 \  2008-01-06

my php script is :

code]$sql="SELECT " 
. "WEEK( start_day) AS wDebut,DAYOFWEEK( start_day) AS wsDebut," 
. "TO_DAYS( start_day) AS dDebut,TO_DAYS( end_day) AS dFin, year( start_day) AS yDebut,year( end_day) AS yfin 
. " FROM table";
$requete = $connexion->query($sql);
$result = $requete -> fetchALL();


for( $i=1; $i<53; $i++ ) 
         $nbrdays[ ] = 0; 

             foreach ($result as $row)
                 {
                   
                   
                    
                    $week = $row['wDebut']; 
                    $dayweek = $row['wsDebut']; // dimanche = 1 
                    $startday = $row['dDebut']; 
                    $end_day = $row['dFin']; 


                     					
                         
                      for( $jour1 = $startday ; $jour1 <= $endday; $jour1++ ) 
				  
                                 {
   
                           if( $dayweek > 1 AND $dayweek <=7  ) 
                           $nbrdays7[ $week]++; 
		               $jourSemaine1++; 
                           if( $dayweek > 7 ) 

                                         {
	$week++; $dayweek = 1;
			     
	               }
                                        }

               }

 

the script show nbr of days by week so i have result like this

week1:  200 days;

week2 :300 days

...

my script work but when startday and endday have same year

but like first line in my sql table i have error cause i have 2 different years

for exemple in this years week 1 start in 2007-12-31  so i have to find solution in this case

i wait for your help and thak u a lot

[TransmogriBenno]

I would suggest instead of doing a loop through all the days to work out how many sundays have passed, you just get the number of weeks passed by dividing the number of days passed by 7 (round down), and then since you know what day of the week the starting day was, you can work out if another sunday has passed during the remainder of days. Presumably this would be faster, as you essentially only have 2 calculations per row.

 

There's obviously a significant amount of code missing, and judging by the foreign language (French?) comments, I'm assuming you've changed variable names to make the script readable (I guess $jourSemaine1 should be $dayweek, otherwise the code doesn't make a lot of sense). I get the feeling that you're counting the number of days in each week that has transpired in $nbrdays, though I'm not sure why. You initialise it with 52 values, all zero, so I'm assuming later in the code you're expecting it to have only 52 elements, which is not the case if the total number of weeks you cycle through is > 52.

[prince198]

thank you for your answer

first i'm french that's why there is some french variables in french

and i think i found solution but of course i nedd your opinion

 

i juste replace  $week = $row['wDebut'];  by $week =1;

and  giving some condition for start week 1 in day like this years start day is 31-12-2008

 

finelly i need some help to transform my script to class because i have  to repeat this script 6 times because my table is like this:

 

startday  /  endday/  condition

 

to explain you i have to count number by type of person for exemple student, teacher....

 

so for my case i have to repeat this script 6 times to put result in 6 <td> in table

[cooldude832]

I would do it more like this

 

1) Store in MySQL all special days (days not to be counted)

2) Run a query to return the count of "special days" in your date span

3) Use php to get the number of days from your Start to End Date (Date function with UNIX clock make it simple)

4) Subtract the "count" from mysql

 

done.

[prince198]

hi

i tried many solution like date_diff in mysql and function workday  in php manual but i found that my script work to resolve my probleme

i don't know how to do with your solution but if u can help me and show me some script will be welcome

 

thank u for your  response

[gigas10]

Turning the dates into seconds usually works pretty good

60*60*24*365.

Although this isnt completely accurate because of leap years

[prince198]

thanks

but i think function to_days(date)  that i use in my sql has same function

all my probleme is the date of begin date have differnet year from end dat :-\

[discomatt]

So you just want to find out how many days have passed between two dates, not counting say, sunday?

 

Easy enough.

 

<?php

$start	= '19 Dec 2001';
$end	= '24 May 2002';

# Convert dates to unix timestamps
$stamp_start = strtotime( $start );
$stamp_end	 = strtotime( $end );

# Subtract difference
$elapsed = abs( $stamp_end - $stamp_start );

# Convert seconds to days
$elapsed = floor( $elapsed / 86400 );

# Remove 1 day per week
$elapsed = $elapsed - floor( $elapsed / 7 );

# Let's check if the start or end day was a sunday
if ( date( 'l', $start ) == 'Sunday' )
$elapsed--;
if ( date( 'l', $end ) == 'Sunday' )
$elapsed--;

echo 'The amout of days between '. $start .' and '. $end .' without Sundays is '. $elapsed;

?>

[prince198]

thank you discomatt

i have to say that i never tried function strotime before and it's good script

but i don't look for number total  day between 2 days and i will explain you now with details my problem.

i start with sql table that contains :

 

start_day  \      end_day

 

2007-12-01    2008-01-03

2008-01-01    2008-01-06

.......

 

and in results when i click in year 2007 will put

 

week 50 :6 days

week 51 :6 days..

of course the others week < 50  are 0

 

when i click in year 2008 will put as result

week 1 : 6 days ( week 1 in 2008 start 2007-12-31 )

week 2: 0

 

like i had written before my script work perfect but when day start and day end are in same year but when i have dierent year i have errors

i look to calcultate number days by week

my table may contain thousands and thousnads line .

i hope that u unterstand me and i hope that u can help me