Deleteing entries from mysql db after 7 days

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
qanimal
Forum Newbie
Posts: 1
Joined: Tue Oct 19, 2004 1:37 pm

Deleteing entries from mysql db after 7 days

Post by qanimal »

feyd | Help us, help you. Please use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


I am trying to write a script to delete entries from a mysql db after seven days.  Getting/comparing/deleting the data is not the problem.  The problem is how do I get the date seven days ago?  

My table has a timestamp field, so I just need to write a script that determines the current date/time and compare it to this field. I've already determined that I need to get the current date/time in mysql or I will have trouble with different formats.  I found the DATE_SUB function in the MySql manual, which looks like it does what I need.  Nut my problem is how to get that info into PHP.  To make things easier for this post, lets use this simple code:

Code: Select all

$sqlsevenago = "SELECT DATE_SUB(NOW(), INTERVAL 7 DAY)";
$myresult = mysql_query($sqlsevenago);
$sevenago = ?????  //I need to know what goes here.
echo "$sevenago";
As you may know, If I simply use *echo "$myresult";* it will write "Resource ID #x" to the screen.

If anyone can help me figure out how to make the above example work, I should be fine.

Thanks in advance.


feyd | Help us, help you. Please use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Untested, but logically, it should work.

Code: Select all

<?

$last_week_stamp = time() - (7 * 86400);
$last_week_date = date('Y-m-d',$last_week_stamp);

$sqlsevenago = "SELECT * FROM your_table WHERE date_field >= '$last_week_date'"
?>
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

something similar to

Code: Select all

$sql = "DELETE" . " FROM `table_name` WHERE UNIX_TIMESTAMP(`date_column`) <= UNIX_TIMESTAMP(NOW(), INTERVAL 7 DAY)";
search the forums for posts by myself involving the word "week"
Post Reply