Deleting records after 3 months

gobbles

Hey Peoples,

I have a bit of a query for you PHP peoples out there.

I have a table which has a field called "regdate" ( Registration Date ), This is put in by the php as follows

date("d/m/y")

So an example of an entered date would be: 14/02/07

I want to run a php script via cron everyday, that deletes records that are 3 months or more old.

I have the code to connect to the database / table as follows, but that is as far as i got.

<?php
$location = "localhost";
$username = "username";
$password = "password";
$database = "database";

$conn = mysql_connect("$location","$username","$password");
if (!$conn) die ("Could not connect to database server");
mysql_select_db($database,$conn) or die ("Could not open database");
?>

Any help on this problem would be greatly appreciated.

Cheers

bradgrafelman

Change the column to a "DATE" type.
On the MySQL manual page for Date and Time Functions, look at the very first sample query (under the index of functions). It shows how to SELECT rows where the datefield has a date within the last 30 days. Change the query to a DELETE query, and reverse the logic (so it's 3 months or more, not less than or equal to three months).

gobbles

Hmmmm Is there anyway that it can be done using the current format?

rikmoncur

bradgrafelman's idea is the best - then in your sql use something like:

DELETE FROM yourtable WHERE regdate < (CURDATE() - INTERVAL 90 DAY)

To do it without changing the format, I suspect you'd have to use strtotime() to convert your date to a timestamp.

bradgrafelman

rikmoncur wrote:
then in your sql use something like:

DELETE FROM yourtable WHERE regdate < (CURDATE() - INTERVAL 90 DAY)

Or, more accurately:

DELETE FROM yourtable WHERE regdate < (CURDATE() - INTERVAL 3 MONTH)

gobbles wrote:
Hmmmm Is there anyway that it can be done using the current format?

Well... I suppose you could try to convert the string into the proper DATE format, and then use some CAST'ing magic to make MySQL think it is a DATE, and then compare that... but why go through that convoluted process instead of using a smarter table structure?

gobbles

Okie Dokie ... If i was to use that new field structure, what would i replace the date("d/m/y") with in my code to insert the date?

bradgrafelman

Well, if you're just inserting the current date, use MySQL's CURDATE() function instead.

gobbles

Ok ... I changed all the files i needed to change and then in my single script i had the following

MySQL query

$query = "DELETE FROM table WHERE regdate < (CURDATE() - INTERVAL 3 MONTH)";
$result = mysql_query($query);

and it seemed to delete all the dates inside the 3 month interval and left some other ones like this: 2014-02-07

bradgrafelman

Well, it did what you told it to - you didn't say anything about future dates.

All you need to do is add an 'OR' onto that last condition that checks if the regdate is greater than the current date plus three months.