deleting time from time

VooDooRT

Since I accidently posted this in the wrong forum, here it is:

I'm trying to get a date that's 60 days in the past and bring it up in a while statement and send a SQL function to delete anything over 60 days in my database, however, when I echo the date, all I get it 1969-12-31

Here is the code:


<?php
include("db_connect.php");

$result = @mysql_query("SELECT date_rec FROM orders WHERE rec = '1'");
while($days = mysql_fetch_array($result)){
$today = date( "Y-m-d", time() );
$yesterday = mktime (0,0,0,date("Y") ,date("m")-2,date("d")); 
$before = date("Y-m-d",$yesterday);
if ($days['date_rec'] >= $before){
echo"<br>$before";
$result1 = @mysql_query("DELETE * FROM orders");
}
echo("
<body bgcolor='#00004a'><center><font color=#cccccc size=1 face=arial>$before<br>

All received items have been deleted from the database. 
            ");
}
//<META HTTP-EQUIV=\"Refresh\" CONTENT=\"5; URL=churchs_options.htm\">
?>

Ok, I got everything to work, but now I can't get it to delete the entries. Ideas?

<?php
include("db_connect.php");
$num = 1;
$result = @mysql_query("SELECT date_rec FROM orders WHERE rec = '1'");
while($days = mysql_fetch_array($result)){

$today = date( "Y-m-d", time() );
$yesterday = mktime (0,0,0,date("m")-1 ,date("d"),date("Y")); 
$before = date("Y-m-d",$yesterday);
$recd = $days['date_rec'];
if ($recd <= $before){
$sql = 'DELETE FROM orders WHERE date_rec < $before';
@mysql_query("$sql") or die ("<p>$sql:  $recd is before $before<P>".mysql_errno().": ".mysql_error()); 
$num++;
}
}
echo("
<body bgcolor='#00004a'><center><font color=#cccccc size=1 face=arial>
<META HTTP-EQUIV=\"Refresh\" CONTENT=\"5; URL=churchs_options.htm\">
$num items have been deleted from the database.  Please wait while you are forwarded back to the admin menu.
            ");
?>

trooper

Sorry but can't give a definitive answer now but just a pointer

look for the TO_DAYS() mysql function it will help you greatly with what you want to do

I'll try and get back to give a better explination

Just a small pointer print out your $sql and see what it returns

That will help with your debugging

HTH

VooDooRT

I tried most of that already and no luck. The TO_DAYS() didn't work at all, but I may have been doing it wrong, heck if I know. Darn SQL...lol

I printed out my SQL on error and it came up just how I have it printed, except it replaces a date in the $before variable.

juschillinnow

check two things for me real quick....

1) use this instead

$sql = "DELETE FROM orders WHERE date_rec < '$before'";

check how the date is stored on the database. are you sure it has dashes in it, or is it a solid number?

ok, I lied....check this also

you are using months days and years. which the computer is reading as 022703 and you are judging it against a date like 120602. Now as a date, as humans we recognize it as the latter before the first. What you probably need to do is change both dates that are being compared into seconds with maketime and then see which is less.

make sense?

VooDooRT

FINALLY GOT IT!!!! WOOOOHOOOO!

Thanks for all the help guys!

<?php
include("db_connect.php");
$num = 1;
$result = @mysql_query("SELECT date_rec FROM orders WHERE rec = '1'");
while($days = mysql_fetch_array($result)){

$today = date( "Y-m-d", time() );
$yesterday = mktime (0,0,0,date("m")-2 ,date("d"),date("Y")); 
$before = date("Y-m-d",$yesterday);
$recd = $days['date_rec'];
if ($recd <= $before){
$sql = "DELETE FROM orders WHERE date_rec < '$before' AND rec = '1'";
@mysql_query("$sql") or die ("<p>$sql:  $days[date_rec] is before $before<P>".mysql_errno().": ".mysql_error()); 
$num++;
}

}
if ($num == '1'){
echo("
<body bgcolor='#00004a'><center><font color=#cccccc size=1 face=arial>
<META HTTP-EQUIV=\"Refresh\" CONTENT=\"5; URL=churchs_options.htm\">
There are no items in the datbase that have been received over two months ago.  Please wait while we forward you back to the admin menu.
			");
}
else{
echo("
<body bgcolor='#00004a'><center><font color=#cccccc size=1 face=arial>
<META HTTP-EQUIV=\"Refresh\" CONTENT=\"5; URL=churchs_options.htm\">
$num items have been deleted from the database.  Please wait while you are forwarded back to the admin menu.
			");
}
?>