Menu
I found the exact same question here.
But it isn't working for me. I've modified it a bit, manipulated it, and I can't figure it out. I'm trying to remove rows that are over a day old. Here is my code:
if (isset($_POST['prune'])) {
$sql = "DELETE FROM logs WHERE time < date('now', '-1 days')";
mysql_query($sql);
echo 'Logs older than one day removed.';
}
Fairly simple question I suppose, but its bugging the hell out of me. I would appreciate any help.
In case it makes a difference, the column is a TIMESTAMP type.
EDIT: Apparently I'm an idiot. The question I linked you to relates to SQLite3. So now my question is, how can I do this in MySQL?
722
Let say you want to delete data that over 31 days old. CREATE TABLE delete_keys SELECT id FROM my_table WHERE 1=2; INSERT INTO delete_keys SELECT id FROM ( SELECT id FROM my_table WHERE time_stored < (UNIX_TIMESTAMP() - 2678400) ORDER BY time_stored ) A LIMIT 100; ALTER TABLE delete_keys ADD PRIMARY KEY (id); DELETE B.
To delete all rows older than 30 days, you need to use the DELETE with INTERVAL. Use < now() i.e. less than operator to get all the records before the current date.
To delete rows in a MySQL table, use the DELETE FROM statement: DELETE FROM products WHERE product_id=1; The WHERE clause is optional, but you'll usually want it, unless you really want to delete every row from the table.
The DELETE Statement in SQL is used to delete existing records from a table.
You can subtract an interval:
DELETE FROM logs WHERE time < now() - interval 1 day
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
