CODEWITHSUNDEEP
phpmysqltimestampsql-delete
faq
faq

Reputation: 3086

A not working mysql delete query. Why?

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'");

Upvotes: 1

Views: 979

Answers (5)

Abhay
Abhay

Reputation: 6645

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

Upvotes: 1

Nicola Cossu
Nicola Cossu

Reputation: 56397

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

Upvotes: 1

Francois Deschenes
Francois Deschenes

Reputation: 24989

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 <.

Upvotes: 1

Optillect Team
Optillect Team

Reputation: 1747

I'm not sure about MySQL, but probably you need something like that:

DELETE FROM $tbl WHERE DATEDIFF('$now', time) > INTERVAL 1 DAY

Upvotes: 1

James C
James C

Reputation: 14159

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)

Upvotes: 0

Related Questions