Menu
How to delete mysql record older than 30 days? my code will delete all the records even which are inserted yesterday.
require('../conn_db.php');
mysql_select_db("my_news",$conn);
mysql_query("SET NAMES utf8");
mysql_query("DELETE FROM my_news WHERE date < DATE_SUB(NOW(), INTERVAL 1 MONTH)");
mysql_close("my_news");
And mysql table
date int(10)
1321095600
1322107200
...
1328288400
1328290440
592
You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the condition in the WHERE clause. You cannot use ORDER BY or LIMIT in a multiple-table DELETE .
Another way to delete multiple rows is to use the IN operator. DELETE FROM table_name WHERE column_name IN (value 1, value 2, value 3, etc...); If you want to delete all records from the table then you can use this syntax.
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.
MySQL uses yyyy-mm-dd format for storing a date value. This format is fixed and it is not possible to change it. For example, you may prefer to use mm-dd-yyyy format but you can't. Instead, you follow the standard date format and use the DATE_FORMAT function to format the date the way you want.
First off, if you really want to delete records older than 30 days, use INTERVAL 30 DAY instead, when you use INTERVAL 1 MONTH you will delete records added on Mars 31st, when it's April 1st.
Also, your date-column is of type int, and DATE_SUB() will return a date in this format YYYY-MM-DD HH:MM:SS, so they are not comparable. You could do this to work around that problem:
DELETE FROM my_news WHERE date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))
Your MySQL Table does not store a date, but rather a unix timestamp (judging from the data you have provided). To delete do the following:
mysql_query("DELETE FROM my_news WHERE date < ".strtotime('-1 month'));
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
