store values

skyloon · #1 06-Jul-2003, 00:37

How can I store this value in my database? For example, this script will calculate the workdays only, when I enter 'Date From' and 'Date To' into the text box, it calculate the duration between 'Date From' and 'Date To', then store the 'Date From','Date To' and the 'Duration' into the database. But it only can store 'Date From' and 'Date To', it cannot store the 'Duration'. For example, the result is: "There are 6 work days from 2003/07/09 to 2003/07/16". How to store "6" into the 'Duration' column.

----------------------------------------------------------------------------------
form1.php (This form has two input boxes; FromDate and ToDate. When I click the submit button, it posts the two values into form2.php.
----------------------------------------------------------------------------------

----------------------------------------------------------------------------------
form2.php (It should calculate the duration and store three values into the database.
----------------------------------------------------------------------------------

<? $Connect = mysql_connect("localhost","root","");
mysql_select_db("mw");
$result=mysql_query("insert into Date1 (FromDate, ToDate, Duration) values ('$FromDate', '$ToDate', '$Duration')");
mysql_close();
?>

<?php
function count_workdays($date1,$date2){
$firstdate = strtotime($date1);
$lastdate = strtotime($date2);
$firstday = date(w,$firstdate);
$lastday = date(w,$lastdate);
$totaldays = intval(($lastdate-$firstdate)/86400)+1;

//check for one week only
if ($totaldays<=7 && $firstday<=$lastday){
$workdays = $lastday-$firstday+1;
//check for weekend
if ($firstday==0){
$workdays = $workdays-1;
}
if ($lastday==6){
$workdays = $workdays-1;
}

}else { //more than one week

//workdays of first week
if ($firstday==0){
//so we don't count weekend
$firstweek = 5;
}else {
$firstweek = 6-$firstday;
}
$totalfw = 7-$firstday;

//workdays of last week
if ($lastday==6){
//so we don't count sat, sun=0 so it won't be counted anyway
$lastweek = 5;
}else {
$lastweek = $lastday;
}
$totallw = $lastday+1;

//check for any mid-weeks
if (($totalfw+$totallw)>=$totaldays){
$midweeks = 0;
} else { //count midweeks
$midweeks = (($totaldays-$totalfw-$totallw)/7)*5;
}

//total num of workdays
$workdays = $firstweek+$midweeks+$lastweek;

}

/*
check for and subtract and holidays etc. here
...
*/

return ($workdays);
} //end funtion count_workdays()

$date1 = "$FromDate";
$date2 = "$ToDate";

echo "There are ".count_workdays($date1,$date2)." work days from $date1 to $date2";

?>

JdS · #2 06-Jul-2003, 02:14

So what you're saying is that the user is submitting date (to and from) in this format: 2003/07/09? And you want to calculate the days between; excluding Saturdays and Sundays?

If yes, gimme some time to work it out and I will post here again if someone doesn't already beat me to it

JdS · #3 06-Jul-2003, 06:27

Try this:

PHP Code:


<?php

/*  the function: calculate_work_days( $from, $to )
    $from and $to ideally should be in JULIAN DAY format
    but I have written it to accept strings too... */

function calculate_work_days( $from, $to )
{
  // just incase you're passing dates in '2003/12/31' formats
  if( is_string($from) )
  {
    $from = unixtojd( strtotime($from) );
  }
  if( is_string($to) )
  {
    $to = unixtojd( strtotime($to) );  
  }

  // $from and $to already in JULIAN DAY format!
  $duration = ( $to - $from ) + 1;
  
  if( $duration < 7 )
  {
    $days = $duration;
    for( $i=0; $i<$duration; $i++ )
    {
      $dow = jddayofweek( $from, 0 );
      if( $dow == 6 || $dow == 0 )
      {
        --$days;
      }
      ++$from;
    }
    unset( $i, $dow );
  }
  elseif( $duration == 7 )
  {
    $days = 5;
  }
  else
  {
    $dow = jddayofweek( $from, 0 );
    $days = ( floor($duration / 7) * 5 ) + ( $duration % 7 );
    if( $dow == 6 ) // Saturday
    {
      $days -= 2;
    }
    elseif( $dow == 0 ) // Sunday
    {
      --$days;
    }
    unset( $dow );
  }
  // done...
  unset( $from, $to, $duration );
  return ( $days < 0 ? 0 : $days );
}


/*  Example use: */
$duration = calculate_work_days( '2003/07/09', '2003/07/16' );

// should return 6!
?>

This function does not include any user input validation so you must extend that to your script if you choose to use this function.

JdS · #4 07-Jul-2003, 05:54

I am very interested to know if this function works for you?

daba78 · #5 20-Jul-2005, 06:54

I've a problem with the script, because i want to count to Workdays an substract the holidays. Does anyone has a answer for me.

Thanks for helping me.

Daba78

JdS · #6 20-Jul-2005, 08:38

Hello daba78,

What is the exact problem with this example? Note that I wrote this function almost 2 years ago, in 2003.

I am actually interested to re-write this function and I will probably do that later. If you can comment with what you found out, that will help.

daba78 · #7 23-Jul-2005, 03:29

Hello JdS

The script your wrote works fine, but i need a function that substract the holidays like New Year, Easter Days etc.

Daba78

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Including Maps and strings??	maddie	CPP / C++ Forum	17	05-Jul-2004 06:25
paasing values in fk_t_diectories	zuzupus	MySQL / PHP Forum	6	12-Aug-2003 07:12
getting values	zuzupus	MySQL / PHP Forum	2	07-Aug-2003 04:33

store values

PHP Function to calculate work days