Need help creating datediff function to not show weekends

sleepyw

I hope this doesn't get duplicated - I wrote this up and submitted it, but it never showed up on the page.

I'm new to PHP and have been to tens of pages online that supposedly explain how to create a datediff function to show the difference between 2 dates, but none of them seem to do what I need.

I have an HTML form that includes a $start_date and $end_date (in yyyy-mm-dd format). When submitted, the following PHP page should calculate the difference between those 2 dates in working days (no weekends) and create the new value called $duration. Below is a snippet of the code from the submitting PHP page that should give an idea of what I'm trying to do (although this obviously doesn't work):

//$_GET['all the variables from the HTML form'];
$start_date = $_POST['start_date'];
$end_date = $_POST['end_date'];

$duration = datediff($_POST['end_date'], $_POST['start_date']); //but this needs to only include weekdays (non-public holiday weekdays if possible)

// update data in mysql database
$sql="UPDATE table_name SET start_date='$start_date', end_date='$end_date', duration='$duration''";
$result=mysql_query($sql);

I've seen snippets of code online that relates to this, but none show a whole function and value placement from start to finish and I haven't been able to piece it together.

Any help here would be very much appreciated.

Weedpacket

sleepyw wrote:
I hope this doesn't get duplicated - I wrote this up and submitted it, but it never showed up on the page.

When you posted you would have been shown a notice saying that it might take a few minutes for the post to appear. Four minutes isn't that bad.

Besides, this doesn't appear to be a database issue per se (except perhaps for formatting the duration as an SQL date/time string, but that's what [man]date[/man] is for). So I'll move it to the Coding forum.

Oh, and here's a thread that might be useful:
http://www.phpbuilder.com/board/showthread.php?t=10341190&highlight=date+difference+work+days

sleepyw

My apologies for the duplication. I've never been to a board that approved threads first, so I didn't pay much attention to the initial message that popped up the first time, so when I didn't see the thread on the next page, I thought maybe I clicked the wrong button and my entry got canceled.

I looked at that other thread, but it doesn't help. It shows absolute values for the dates while mine are variable and input from an HTML form. I also don't want to echo the result - I need it recorded to the database as a new field.

Here's some code I found elsewhere that worked when I showed the result on the page, but I couldn't get it to write the result to the db and can't see from this how to eliminate weekends:

function daysDifference($endDate, $beginDate)
{

   //explode the date by "-" and storing to array
   $date_parts1=explode("-", $beginDate);
   $date_parts2=explode("-", $endDate);
   //gregoriantojd() Converts a Gregorian date to Julian Day Count
   $start_date=gregoriantojd($date_parts1[1], $date_parts1[2], $date_parts1[0]);
   $end_date=gregoriantojd($date_parts2[1], $date_parts2[2], $date_parts2[0]);
   return $end_date - $start_date;
}

Weedpacket

sleepyw wrote:
I looked at that other thread, but it doesn't help. It shows absolute values for the dates while mine are variable and input from an HTML form. I also don't want to echo the result - I need it recorded to the database as a new field.

Doesn't make a difference where the values come from: the way the code works would be the same. And there's no law that says the result has to be echoed. Those bits of LordShryku's code are only there so that the important bit can be seen to be working. The important bit being:

$days = 0;
while($time1 < $time2) {
   $chk = date('D', $time1); # Actual date conversion
   if($chk != 'Sat' && $chk != 'Sun')
      $days++;

   $time1 += 86400; # Add a day
}

Not surprisingly, perhaps, I think I still prefer my solution there better.

sleepyw

I get a 500 Internal Server Error when I use that code.

Weedpacket

Let's see how you used it.

sleepyw

Pretty much the same way you typed it up, but changed the variables:

//$_GET['all the variables from the HTML form'];

$start_date = $_POST['start_date'];
$end_date = $_POST['end_date'];

$duration = 0;
while($start_date < $end_date) {
   $chk = date('D', $start_date); # Actual date conversion
   if($chk != 'Sat' && $chk != 'Sun')
      $duration++;

   $time1 += 86400; # Add a day
}

// update data in mysql database
$sql="UPDATE table_name SET start_date='$start_date', end_date='$_POST[end_date]', duration=$duration' WHERE id='$id'";

Weedpacket

So does the user actually enter Unix timestamps (as used by [man]date[/man]) for start date and end date? Reread LordShryku's example.

You changed the name of $time1 to $start_date everywhere except in one place. Now PHP has no way to tell when the loop should stop; so it either runs forever or it doesn't run at all.

sleepyw

I'll have to check the time1 thing - I had to tre4copy what I put in to this thread so I may just have copied it incorrectly.

The users enter yyyy-mm-dd format.

EDIT TO ADD:

I guess I'm an idiot. I updated the variable in that code, but all it does now is turn my entry to 0 - as is alludes to in the code. 😕

$duration = 0;

sleepyw

OK - I got it to work finally! One problem though - I have this formula repeated in several instances and in one situation, the calculation is getting ignored because 1 date comes before another date. Is there a way to show the difference in dates regardless of which comes first? Even if it shows as a negative number?

Weedpacket

Yes, there is a way.

Weedpacket

Oh, you want to be told how, right? 🙂

Have a variable $dates_swapped set to false.

Before starting the loop, check to see if the "end date" is before the "start date". If it is (a) set the $dates_swapped variable to true. (b) Swap the "end date" and "start date" values.

After the loop, look at $dates_swapped. If it's true, then the duration should be made negative.

sleepyw

Thanks for all your help!