MYSQL- php- Need quick way to delete X day old entries. Ideas?

[physaux]

Hey guys, ok so here is what I want to do.

 

I want to run a cron job every day to run a script that will 'filter' out old entries of my mysql table based on how long ago they were first created. I know how to run the cron job, my problem is with the code on how to do it.

 

-is there a simple, one line way to do this?

-or will I have to make a variable with each row, 'date_created', then calculate which entries are older than X days, and then remove them?

 

Anyone have experience with this kind of thing? I just need an idea, I can code it no problem.

 

Thanks for your help!

[JustLikeIcarus]

The query would be something along the lines of...

 

delete from your_table where date_created < date_sub(curdate(), 10 days)

 

This would delete records older than 10 days.

[physaux]

Thanks so much, that is exactly what I needed. You saved me lots of time! Thanks again.

[physaux]

Ok so I got around to implementing this but I am having problems. It is not deleting the entries. However, this is because I only have entries from today. I am trying to temporarily get a statment like this:

 

$numdays = 1;
$sqlquery = "DELETE * FROM $table WHERE date_created < date_add(CURRENT_DATE, $numdays Day)";
$result = mysql_query($sqlquery);

 

to delete my entries successfully. But it is not working.

 

**I know my sql query is being processed, becasue I tested a truncate command on $table, and it cleared the data as I wanted.

 

So guys please help me. What am I doing wrong?

 

EDIT: I have tried the following variations of sqlqueries, none of which delete the entries:

//$sqlquery = "DELETE * FROM $table WHERE date_created < date_add(CURRENT_DATE, $numdays Day)";
//$sqlquery = "DELETE * FROM $table WHERE date_created < date_add(curdate(), $numdays Day)";
//$sqlquery = "DELETE * FROM $table WHERE date_created < date_add(CURRENT_DATE, $numdays Days)";
//$sqlquery = "DELETE * FROM $table WHERE date_created < date_add(curdate(), $numdays Days)";

[ignace]

It's days not Day plus

 

DELETE * FROM $table WHERE date_created < date_add(CURRENT_DATE, $numdays Day)

 

Is the same as:

 

DELETE * FROM $table

[physaux]

Ok this is wierd. Here is my entire code:

 

<?php
ini_set('display_errors',1);
error_reporting(E_ALL|E_STRICT);


$numdays = 2;


$DBhost = "XXX";
$DBuser = "XXX";
$DBpass = "XXX";
$DBName = "XXX";
$table = "XXX";


mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");
@mysql_select_db("$DBName") or die("Unable to select database $DBName");

//$sqlquery = "delete * from $table where date_created < date_sub(CURRENT_DATE, $numdays Days)";
//$sqlquery = "DELETE * from $table WHERE date_created < DATE_FORMAT( CURRENT_DATE - INTERVAL $numdays DAY, '%Y/%m/%d' ) ";
//$sqlquery = "DELETE * FROM $table WHERE date_created < date_add(CURRENT_DATE, $numdays days)";
$sqlquery = "DELETE * FROM $table";


$result = mysql_query($sqlquery);

mysql_close();

?>

 

Now even the simple one won't work:. "Delete * from $table"! Why is that? Very strange.

But, when I do "TRUNCATE TABLE $table", it DOES WORK. What is wrong?? This makes no sense!!  :'( :'( :'(

[JustLikeIcarus]

Now even the simple one won't work:. "Delete * from $table"! Why is that? Very strange.

But, when I do "TRUNCATE TABLE $table", it DOES WORK. What is wrong?? This makes no sense!!  :'( :'( :'(

 

Well

Delete * from $table

  is not a valid delete statement.  It would just be

Delete from $table

[physaux]

Ok my sql query is now

 

$sqlquery = "DELETE FROM $table WHERE date_created < date_add(CURRENT_DATE, $numdays days)";

 

But it is still not deleting any of the entries in my table.

Anyone see how I can fix?

[ignace]

do:

 

echo $sqlquery;

 

after $sqlquery = "DELETE .. and tell us what is says

[physaux]

Ok I echo'd the value. Here is the output:

 

DELETE FROM splittest WHERE date_created < date_add(CURRENT_DATE, 2 days)

 

Here is the exact code copy pasted so you can see where echo was:

 

$sqlquery = "DELETE FROM $table WHERE date_created < date_add(CURRENT_DATE, $numdays days)";
echo $sqlquery;


$result = mysql_query($sqlquery);

mysql_close();

[physaux]

so was my echo a proper MYSQL syntax?

 

I still haven't found the problem  :'(

[ialsoagree]

Have you tried echo mysql_error(); after your $result = line?

 

Also, you realize that you're doing date_add right? Every date_created is going to be a smaller number than a date in the future (in other words, your script will delete every entry that wasn't created at least $numdays days in the future).

[physaux]

Yes, I am doing date_add temporarily because I only had recent results, and I wanted to make it delete the records. As you say it should delete them all, but it doesn't. I'm about to add the

 

echo mysql_error();

 

to the code, and see what happens. I'll edit this message when I return

 

EDIT: ok it gave this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2 days)' at line 1

 

any ideas??

[ialsoagree]

Give this a go:

 

DELETE FROM $table WHERE date_created < date_add(CURRENT_DATE, INTERVAL $numdays day)

 

(Note singular 'day' and not 'days').

 

The MySQL 5.1 manual entry for date_add:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add

[physaux]

Ok my error now is

 

Unknown column 'date_created' in 'where clause'

 

Hmm I'm really confused. Could it be the settings of my table? idk

[ialsoagree]

That error means that you don't have a column named date_created in that table, perhaps it's named something else?