prince198 Posted July 7, 2008 Share Posted July 7, 2008 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 Quote Link to comment Share on other sites More sharing options...
TransmogriBenno Posted July 7, 2008 Share Posted July 7, 2008 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. Quote Link to comment Share on other sites More sharing options...
prince198 Posted July 7, 2008 Author Share Posted July 7, 2008 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 Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted July 7, 2008 Share Posted July 7, 2008 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. Quote Link to comment Share on other sites More sharing options...
prince198 Posted July 7, 2008 Author Share Posted July 7, 2008 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 Quote Link to comment Share on other sites More sharing options...
gigas10 Posted July 7, 2008 Share Posted July 7, 2008 Turning the dates into seconds usually works pretty good 60*60*24*365. Although this isnt completely accurate because of leap years Quote Link to comment Share on other sites More sharing options...
prince198 Posted July 7, 2008 Author Share Posted July 7, 2008 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 :-\ Quote Link to comment Share on other sites More sharing options...
discomatt Posted July 7, 2008 Share Posted July 7, 2008 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; ?> Quote Link to comment Share on other sites More sharing options...
prince198 Posted July 7, 2008 Author Share Posted July 7, 2008 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.