Today minus sixty days?

[VooDooRT]

I'm trying to write a function that will check a SQL query for a date entered previously and see if it is more than 60 days in the past. I'm having trouble with it though...of course...

It's not returning anything...Help?

$today = date( "Ymd", time() );
  $result = @mysql_query("SELECT TO_DAYS($today)-TO_DAYS(date_rec) FROM orders WHERE date_rec !=''");
  while($row = mysql_fetch_array($result)) {
  return $row;
echo("<font color=#cccccc size=1 face=arial>$days</font><br>");
}

[sijis]

couldn't you try something like this:

 $result = @mysql_query("SELECT * FROM orders WHERE date_rec !=''" AND date_rec <= NOW() - 30 );

i'm just taking a wild guess, but that might give you an idea at least.

[sarah]

Or, you can establish the date -60 days before you do the query:

$todayMinus60=date("Y-m-d",mktime(0,0,0,date(m),date(d)-60,date(Y)));

$result = @mysql_query("SELECT * FROM orders WHERE date_rec !=''" AND date_rec <= $todayMinus60);

[ereptur]

you could also use

$result = @mysql_query("SELECT * FROM orders WHERE date_rec > DATE_SUB(now(), interval 60 day)");

this way, you're making mysql do the date calculations

[VooDooRT]

Well, what I'm trying to do in the end is delete everything in the database more than 60 days past a certain date. I was going to format the date into a format where I can just go 60 days minus the current number(date) using a julain format, but, I figured that wouldn't work because what if the date is 03007 like today. That's mean anything 02365 or before would get delted because 02365 is DEFINATLY less than 03007 minus 60.

Anyway, I want to get rid of everything in the database more than 60 days old.

[diego25]

So play with the queries that the other 3 posters gave you. Instead of a SELECT, you'd be using a DELETE query. The WHERE part would be pretty similar to what they had.

Diego

[VooDooRT]

Ok, this is what I'm getting now:

include("db_connect.php");
$todayMinus60=date("Y-m-d",mktime(0,0,0,date(m),date(d)-10,date(Y)));
$result = @mysql_query("SELECT * FROM orders WHERE date_rec !='' AND date_rec <= $todayMinus60") or die ("<p>$fontString $sql<P>".mysql_errno().": ".mysql_error()); 
  while($row = mysql_fetch_array($result)) {
echo("<font color=#cccccc size=1 face=arial>$row[date_rec]</font><br>");
}

This code is bringing up:

Dec 18, 2002
Dec 17, 2002
Jan 08, 2003
Jan 06, 2003
Jan 06, 2003
Jan 06, 2003
Jan 08, 2003
Jan 06, 2003
Dec 18, 2002
Jan 08, 2003
Jan 08, 2003
Jan 08, 2003
Jan 08, 2003
Jan 08, 2003
Jan 08, 2003

this isn't every entry in the database, and it's also not the ones greater than 10 days in the past. I just used 10 because I don't have anything in the database yet that's over 60 days. I can always change the 10 to 60...lol

Anyway, what's going on here?

[sarah]

Out of couriosity, how do you have the date stored in the database?

[VooDooRT]

Dec 18, 2002

[sarah]

Ooooooh...I think that's the problem...I don't know a lot about MySQL, but I thought that in order for date searches to work properly, they needed to be in the database in this format yyyy-mm-dd. Ideally, the database field type should be the date type too. Can someone confirm this?

[sijis]

in the database is it stored:

Dec 18, 2002
or
12182002

If its stored like "Dec 18, 2002" in the db, its just a string that happens to be a date, but its not a date. I'm sure you could change the "Dec 18, 2002" to "Potatoes" and you wouldn't have a problem. It should look something like 12182002 or 2002-12-18...
(you can't compare "Potatoes" or "Dec 18, 2002" to 2002-12-18 it just isn't possible) its might seem the same, but it isn't.

but sarah is right, you need to have it in yyyy-mm-dd or something of equivalence.

what type if your field in the database? is it TimeStamp? Date? DateTime?

If you dont know or are somewhat confused, look here:

Mysql Column Types:
http://www.mysql.com/doc/en/Column_types.html

DATETIME, DATE, and TIMESTAMP Types.
http://www.mysql.com/doc/en/DATETIME.html

[VooDooRT]

The field type is just TEXT. Should I go back and change my SQL to DATE or should I just re-format the date to be yyy-mm-dd?

[sijis]

if you are just storing dates, i'd change the type to one of the date types:

DATETIME, DATE, and TIMESTAMP Types.
http://www.mysql.com/doc/en/DATETIME.html

Since it seems that you are doing some sort of ordering system, i'd suggest if the date that you want is the date/time when that item/product is ordered, id suggest you TIMESTAMP type.
this way, all you have to worry the date is when you delete old orders.
Using timestamp automatically adds the current date when data is entered in to db.

that should help.

[VooDooRT]

Ok, I changed my SQL to reflect DATE and it's still not working. It's not picking up anything at all.

This is what I have:

$todayMinus60=date("Y-m-d",mktime(0,0,0,date(m),date(d)-10,date(Y)));
$result = @mysql_query("SELECT * FROM orders WHERE date_rec !='' AND date_rec <= $todayMinus60") or die ("<p>$fontString $sql<P>".mysql_errno().": ".mysql_error()); 
  while($row = mysql_fetch_array($result)) {
echo("<font color=#cccccc size=1 face=arial>$row[date_rec]</font><br>");
}

The format in the SQL table is 2003-01-10 and it's set to DATE. I'm not sure what the deal is...hopefully you guys do!

[RedDragon]

you should consider using the unix-time in your database then this would all be very easy

$x = time();

store $x in the database as an integer
then use the following code (you might need to modify it) to delete the matches

//calculate the date
$days = 3;
$todayminusxdays = time()-(86400*$days);

//query
$query = mysql_query("SELECT id FROM tablename WHERE (storedtime<='$todayminusxdays')");

//deleting
while($delete = mysql_fetch_array($query)) {
mysql_query("DELETE FROM tablename WHERE (id='$delete[id]')");
}

[VooDooRT]

Guys, this is still irritating me to no end.

<?php
include("db_connect.php");
$today = date( "Ymd", time() );
  $result = @mysql_query("SELECT TO_DAYS($today)-TO_DAYS(date_rec) FROM orders WHERE rec = '1'");
		echo("
<body bgcolor='#00004a'><center><font color=#cccccc size=1 face=arial>
<META HTTP-EQUIV=\"Refresh\" CONTENT=\"5; URL=churchs_options.htm\">
Orders over 60 days and received have been deleted.
Please stand-by while I forward you back to the Navigation Menu.
			");
	while($days = mysql_fetch_array($result) > 10) {
		@mysql_query("UPDATE orders SET rec = '3' WHERE rec = '2'");
		}
?>

This is what I'm using now but I can't get it to do a darn thing for me. Also, I set my database tables to read as DATE not integer or text anymore. So all the days are in the following format:
2002-01-22

Ideas? Where am I messing this up?

[RedDragon]

why dont you just use the following code

$time = time();
//store $time in db

if you want to display the time use
$time = gmdate(format options, $dbtime);
echo $time;

if you want to get all rows not older than 60 days use
$days=60;
$stamp = time()-(86400$days)
$query = "SELECT FROM table WHERE (time>='$stamp')";

[VooDooRT]

I already have some 190 entries in the database and I don't want to go changing the format now. I know it can be done, but I can't get the while to work.

[RedDragon]

while($days = mysql_fetch_array($result) > 10) {
@("UPDATE orders SET rec = '3' WHERE rec = '2'");
}

this will not work as you want...
if you use
mysql_query("UPDATE orders SET rec = '3' WHERE rec = '2'");
it will update multiple rows in nessasary, so you dont need the loop...

it would make sense to use
while($days = mysql_fetch_array($result) > 10) {
@("UPDATE orders SET rec = '3' WHERE rec = '$days[rec]'");
}
or something simular, in the loop you must use the $days var so it makes sense

also you have to move the html behind the loop and you might try removing the > 10 from your code

[VooDooRT]

Is there a way to convert 2002-01-21 to time()?

If there isn't, then I'll need to figure another way out.

The while statement doesn't seem to be working in any way I try it. It's pissing me off now...