[RESOLVED] Delete rows before a certain date.

[Slyke]

I want to delete all rows in a database that are older then 4 months.

I have a column called 'logdate' which holds the date that the data was entered.

Here's my current code:

$today = date("d/m/Y", strtotime("-120 day"));

mysql_query("DELETE FROM logtable WHERE logdate < '" . $today . "'") or die(mysql_error());

This however deletes everything.

[ngreenwood6]

I would look into the mktime() method. you can set the date to 120 days earlier with that function or you could do it as a time stamp if you store it that way. like this:

$time = time(); //sets the current time
$120_days = 60*60*24*120; //makes 120 days worth of seconds
$120_days_ago = $time - $120_days; //sets it to 120 days ago in seconds

mysql_query("DELETE FROM logtable WHERE logdate < '$120_days_ago'") or die(mysql_error());

[Slyke]

I don't want to sound like a noob... but could you please provide an example? I never really got time calculations...

mktime

[Slyke]

I would look into the mktime() method. you can set the date to 120 days earlier with that function or you could do it as a time stamp if you store it that way. like this:

$time = time(); //sets the current time
$120_days = 60*60*24*120; //makes 120 days worth of seconds
$120_days_ago = $time - $120_days; //sets it to 120 days ago in seconds

mysql_query("DELETE FROM logtable WHERE logdate < '$120_days_ago'") or die(mysql_error());

This would work even if the time format is "d/m/Y"?

[ngreenwood6]

The example I gave is pretty good.

This would work even if the time format is "d/m/Y"?

In my post I said if your store the logdate as a timestamp you can do this. you can convert a timestamp to a "d/m/Y" using the date() function. I always store my dates and times as timestamps because they are easily converted to a date or a time format

[Slyke]

OK, so from now on just store all the times & dates as 1 field & just use a timestamp?

Then I can use the date function:

date("d/m/Y",$theTimeStamp)

It will take a little time to change the database, but if it works this way it's worth it .

[ngreenwood6]

yeah, you are correct. the date() function can also change it to a time.