trying to get date difference after date in php

[4bidden]

Here is the situation.

I have a mySQL database that has a starttime field (datetime), endtime field (datetime), coursecode field (GPR4 for example) and a nice pretty field that has the dates already formatted like December 28-30, 2006.

The database is a listing of all available events occuring at our facility. I am displaying the available dates on the web. I figured I'd make it easy on myself and use the pre-formatted field BUT I forgot something. We have a course called GPR3 and GPR4 (the 3 and 4 refering to a 3 and 4 day course). The GPR3 dates are all GPR3 and GPR4 dates (they just do the first 3 days). So when I select all the info with rows containing both coursecodes, the nice and pretty pre-formatted column still shows the course running 4 days.

So my output is looking like this.

Available GPR3 dates:
May 22-25, 2006
June 5-8, 2006
June 19-22, 2006
June 26-28, 2006
July 5-7, 2006
July 10-13, 2006


As you can see, most of the dates are GPR4 dates and therefore show the course running 4 days.

So how should I go about fixing this. The startdate field returns format YYYY-MM-DD 00:00:00.

The logic basically needs to go

if(enddate - startdate > 3) {
enddate--;
}

And then I can work with it from there. I'm just lost as to what transformations (and how) to do on the datetime format from sql to get there.

Thanks in advance for all help!!!

[darga333]

[!--quoteo(post=368956:date=Apr 26 2006, 03:12 PM:name=4bidden)--][div class=\'quotetop\']QUOTE(4bidden @ Apr 26 2006, 03:12 PM) [snapback]368956[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Here is the situation.

I have a mySQL database that has a starttime field (datetime), endtime field (datetime), coursecode field (GPR4 for example) and a nice pretty field that has the dates already formatted like December 28-30, 2006.

The database is a listing of all available events occuring at our facility. I am displaying the available dates on the web. I figured I'd make it easy on myself and use the pre-formatted field BUT I forgot something. We have a course called GPR3 and GPR4 (the 3 and 4 refering to a 3 and 4 day course). The GPR3 dates are all GPR3 and GPR4 dates (they just do the first 3 days). So when I select all the info with rows containing both coursecodes, the nice and pretty pre-formatted column still shows the course running 4 days.

So my output is looking like this.

Available GPR3 dates:
May 22-25, 2006
June 5-8, 2006
June 19-22, 2006
June 26-28, 2006
July 5-7, 2006
July 10-13, 2006
As you can see, most of the dates are GPR4 dates and therefore show the course running 4 days.

So how should I go about fixing this. The startdate field returns format YYYY-MM-DD 00:00:00.

The logic basically needs to go

if(enddate - startdate > 3) {
enddate--;
}

And then I can work with it from there. I'm just lost as to what transformations (and how) to do on the datetime format from sql to get there.

Thanks in advance for all help!!!
[/quote]


I dont know exactly what you are trying to do. If all it is is to get two different sets of dates, one the GPR4 and one the GPR3.. all you need to do is two diff queries..

//$coursecode is the GPR variable.. so if you create a form to pull up the dates you will use 'coursecode' as the name of the input field.. ex.

Select a GPR type:
<SELECT NAME="coursecode">
<OPTION VALUE="GPR3"> GPR3
<OPTION VALUE="GPR4"> GPR4
<OPTION VALUE="GPR5"> GPR5
</SELECT>



//Then your form processing page should have this code.. tablename being the name of the table you are storing your data in

$coursecode = $_POST[coursecode];

$sql = "SELECT * FROM tablename WHERE coursecode = '$coursecode' ";
$result = mysql_query($sql);
$num_rows = mysql_num_rows($result);
if ($num_rows > 0){
while $row = mysql_fetch_array($result){
echo "Starttime is:".$row['starttime'];
echo "Endtime is:$row['endtime'];
echo "Pretty date is:$row['prettydatefield'];
}
}

Remember when getting certain results to display, its always in the SQL query...
I hope this helps! I'm a newbie so there might be some php syntax error

[4bidden]

Maybe I should simplify what I'm asking....

I am getting a variable such as January 24-27, 2006 fed to me from mySQL, but sometimes I will need to subtract one day from the end making it January 24-26, 2006. I also have available to me the start date and end date in datetime format such as 2006-01-24 00:00:00.

How about this. How can I do math on two dates formatted in mysql datetime in PHP?

[Caesar]

You are much better off storing dates in Unix timestamps...and then doing the math, and then output the results in PHP, after you format them. Makes life easier.

[darga333]

[!--quoteo(post=368997:date=Apr 26 2006, 05:02 PM:name=4bidden)--][div class=\'quotetop\']QUOTE(4bidden @ Apr 26 2006, 05:02 PM) [snapback]368997[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Maybe I should simplify what I'm asking....

I am getting a variable such as January 24-27, 2006 fed to me from mySQL, but sometimes I will need to subtract one day from the end making it January 24-26, 2006. I also have available to me the start date and end date in datetime format such as 2006-01-24 00:00:00.

How about this. How can I do math on two dates formatted in mysql datetime in PHP?
[/quote]

ahhh good question

here is some code i was working with just a few weeks agao.. hopefully it is displayed nice.. anyways its exactly what you want. just assign $date_today variable the variable that you are retrieving from your database.. and where i use 6 days, you would use 1 in your case... its a pretty nifty snipplet of code

//Get current time and split into its individual units
$date_today = date('Y-m-d');
$dateSplit = explode("-",$date_today);
$year = $dateSplit[0];
$month = $dateSplit[1];
$day = $dateSplit[2];

//Next put these date parts into the mktime function to generate a unix time stamp
$timestamp = mktime(0,0,0,$month,$day,$year);

//Calculate the # of seconds in a day then Multiply by the days that we want
$oneDay = 24 * 60 * 60;
$total = 6 * $oneDay;
$newTimestamp = $timestamp - $total;

//Convert our new timestamp back to our original date format with date()
$start_time = date("Y-m-d",$newTimestamp);
//echo $start_time;

[!--quoteo(post=369002:date=Apr 26 2006, 05:17 PM:name=darga333)--][div class=\'quotetop\']QUOTE(darga333 @ Apr 26 2006, 05:17 PM) [snapback]369002[/snapback][/div][div class=\'quotemain\'][!--quotec--]
ahhh good question

here is some code i was working with just a few weeks agao.. hopefully it is displayed nice.. anyways its exactly what you want. just assign $date_today variable the variable that you are retrieving from your database.. and where i use 6 days, you would use 1 in your case... its a pretty nifty snipplet of code

//Get current time and split into its individual units
$date_today = date('Y-m-d');
$dateSplit = explode("-",$date_today);
$year = $dateSplit[0];
$month = $dateSplit[1];
$day = $dateSplit[2];

//Next put these date parts into the mktime function to generate a unix time stamp
$timestamp = mktime(0,0,0,$month,$day,$year);

//Calculate the # of seconds in a day then Multiply by the days that we want
$oneDay = 24 * 60 * 60;
$total = 6 * $oneDay;
$newTimestamp = $timestamp - $total;

//Convert our new timestamp back to our original date format with date()
$start_time = date("Y-m-d",$newTimestamp);
//echo $start_time;
[/quote]


you will notice that the way i had it i used subtraction.. you can use addition to add and get future dates, etc.. the only reason taht it needs to go to the unix timestamp is so it can get the exact time in seconds... then subtract how ever many days in seconds.. if you really look at it it should be self explanitory. hope that helps!

[kenrbnsn]

Why get the unix timestamp this way:
[code]<?php
//Get current time and split into its individual units
$date_today = date('Y-m-d');
$dateSplit = explode("-",$date_today);
$year = $dateSplit[0];
$month = $dateSplit[1];
$day = $dateSplit[2];

//Next put these date parts into the mktime function to generate a unix time stamp
$timestamp = mktime(0,0,0,$month,$day,$year);
?>[/code]
When it can be done in one statement:
[code]<?php $timestamp = strtotime('today'); ?>[/code]
or
[code]<?php $timestamp = time(); ?>[/code]

I have been told that using the time() function is more efficient than the strtotime() function.

Ken

[darga333]

hey i didnt know that function existed.. strtotime(); if i would have known that i would have saved a lot of lines messy code! thanks! hopefully both of our posts help!