Delete entries older than 7 days

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Wernervantonder
Forum Newbie
Posts: 1
Joined: Tue May 08, 2007 3:38 am

Delete entries older than 7 days

Post by Wernervantonder »

Good day to all that help me

I would like to delete all the records in mysql database that is older than 7 days.

How do i go about selecting it in the database ( currentdate - 7) where level = '0'

my database is ctrack1
table is operator
fields is: ID,Username,Password,Date_created,Level

This is what i tried:

mysql_select_db($database_operator, $operator);
$query_delete = "DELETE FROM operator WHERE ((TO_DAYS(CURDATE()) - TO_DAYS(Date_created)) > 7) AND Level='0'";
$delete = mysql_query($query_adminlogin) or die(mysql_error());


the above code generates an error when i execute it stating that im using wrong version of MySQL. i am using for test purposes (as i know it works on my host) EasyPHP 1.8.


Then i tried the following :

$curdate = date("Y.m.d");
mysql_select_db($database_operator, $operator);
$query_cleaunup = "DELETE FROM operator WHERE '$curdate' - 'Date_created' < 7 AND Level='0'";
$cleaunup = mysql_query($query_cleaunup) or die(mysql_error());


When i execute this code it does nothing. but note that when i change the < sigh to > then it deletes all the Level 0 entries!

I just basically want to delete all the entries that is older than 7 days and is lovel 0 in my database. How do i do it? please i am desperate for an answer. I appreciate your response!
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

The SQL you want is:

Code: Select all

DELETE FROM operator WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) > Date_created AND level = '0'
However, I wouldn't recommend doing this. Flag things as deleted, don't actually delete them. Database space is incredibly cheap and having a record of everything that there's ever been is very useful. Plus, if someone makes a mistake and 'deletes' records that weren't meant to be deleted you can simply set them back to a "not deleted" status. Only delete stuff if you're absolutely positive that you will never, ever need it again. And even then make a backup regularly in case you're wrong.
Post Reply