Date calculations

[spyer]

Good day again..

I ask so many questions, don't I?  :-[

thank you for answering them,,

my question today is about the date calculatings and alerts.

I have a table [color=red][b][i][sch][/i][/b][/color] with a record such as this one:








[color=red]schid       eqn           wo       plant           el                   tos            dd         rd         td          id           rnrv     ct      ub     yid   mid[/color]
   [color=blue]6      3125485   22548746    336    D-0001 Dehydrator    OIL     07/12/2006    0           0           0          NULL     0     AMG     1    2[/color]

i was trying to make it update the column [rnrv] with a value [OD] if todays date is bigger than the [dd] value which means Due Date.

i managed to make it work, piece of cake..

what i want is,, for example now is the month of June 06, 2006... and i already made my list of RVs and all records are filled.. i want when the month is over and today's date is 07/01/2006 .. (July 01, 2006),  it looks in the [ct] showed above in the table if it's SET to 0 to UPDATE [rnrv] to the value [OD] ELSE leave it as it is. and so on with the later months and years..

so,, again... IF TODAY's DATE IS A NEW MONTH [color=red][b][i][07/01/2006][/i][/b][/color] AND THE DATE SPECIFIED IN FIELD [color=red][b][i][DD][/i][/b][/color] IS EARLIER MONTH [color=red][b][i][06/12/2006][/i][/b][/color] WHERE FIELD [color=red][b][i][CT][/i][/b][/color] IS SET TO A VALUE OF [color=red][b][i]0[/i][/b][/color] IT UPDATES THE VALUE OF FIELD [color=red][b][i][RNRV][/i][/b][/color] WITH THE VALUE [color=red][i][b][OD][/b][/i][/color] AND SENDS AND E-MAIL AUTOMATICLY TO A PREVIOUSLY SPECIFIDE E-MAIL E.G. [color=red][b][i][[email protected]][/i][/b][/color].

I hope i made it easier for you guys,, though i think i complicated it  ;D

Thanks a lot.

[Caesar]

First, you should not be storing formatted dates in your database. It's just bad practice. You should only ever format dates for display purposes, and store them only as timestamps. It makes for easy comparisons and sorting.

[spyer]

Thanks for the tip. ;)

[spyer]

help please

[spyer]

any one?

[craygo]

[code]<?php
$sql = "SELECT * FROM sch WHERE schid = '$id'";
$res = mysql_query($sql) or die (mysql_error());
$row = mysql_fetch_assoc($res);
$schday = date("d", strtotime($row['dd']));
$schmonth = date("m", strtotime($row['dd']));
$schyear = date("Y", strtotime($row['dd']));
$tday = date("d");
$tmonth = date("m");
$tyear = date("Y");
if($tday > $schday && $tmonth >= $schmonth && $tyear >=schyear && $row['ct'] == 0){
$update_sql = "UPDATE sch SET rnvr = od WHERE schid = '".$row['schid']."'";
$ures = mysql_query($update_sql) or die(mysql_error());
  if(!ures){
  echo "could not update record. No e-mail sent";
  } else {
  echo "Record updated";
  mail($to,$subject,$message,$headers);
  }
} else {
echo " Record is up to date";
}
?>[/code]

Ray