I have a table with 3 rows and one of those contains a unique time code (ex: 1308162911). There are a lot of these records but I want to delete all records which are bigger than one day (AKA 86400 seconds). I have this query but it doesn't work (nothing happens):
$db = mysql_connect($hostname, $db_user, $db_password);
mysql_select_db($database,$db)or die( "Unable to select database");
$now = time() - 86400;
$delete = ("DELETE FROM $tbl WHERE time > '$now'");
Unix timestamp increases as time goes on so your query will delete all records more recent than 24 hours ago, not longer than 24 hours ago.
You should be OK to remove the single quotes around the timestamp value too.
If you're still having a problem please can you include the line of code that executes mysql_query() and the format of the database (output of SHOW CREATE TABLE myTable)
I'm not sure about MySQL, but probably you need something like that:
DELETE FROM $tbl WHERE DATEDIFF('$now', time) > INTERVAL 1 DAY
How about something like this:
$yesterday = strtotime('-1 day');
$delete = "DELETE FROM $tbl WHERE time > FROM_UNIXTIME($yesterday)";
The above query will delete all rows where the "time" value is greater than exactly 24 hours ago. This assumes that the "time" field is a TIMESTAMP, DATETIME or DATE type. If you want to delete records that are older than a day, change the > for a <.
select * from table
where now() - interval 1 day > from_unixtime(unix_timestamp_field)
if this is what you're lookin for convert the select into a delete query
This should work:
DELETE FROM $tbl
WHERE FROM_UNIXTIME(`time`) > DATE_SUB(NOW(), INTERVAL 1 DAY);
Or otherwise, in your code, I think you should remove the single-quotes around $now. However, I think it is a good idea to do it all as part of a MySQL query to avoid any time differences between PHP and MySQL if they are both running in different time-zones