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!
Delete entries older than 7 days
Moderator: General Moderators
-
Wernervantonder
- Forum Newbie
- Posts: 1
- Joined: Tue May 08, 2007 3:38 am
The SQL you want is:
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.
Code: Select all
DELETE FROM operator WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) > Date_created AND level = '0'