Deleting MySQL row if IP matches and less than 20-sec old

[Jeremy_H]

I'm struggling with this MySQL command and was hoping somebody might have some feedback.

I am trying to delete a row in my table if the ip matches, and if that row was created in the last 20 seconds.

The table contains a column called $time that contains time in the datetime format. So a typical entry could be 2006-06-01 11:50:28. This is the time when that row was created. So if the ip address matches, the script would look at this time to see if it occurred within the last 20 seconds.

This is the code that I have, but it doesn't work.

$time=date("Y-m-d H:i:s")-20;
mysql_query("DELETE FROM table WHERE ip='$REMOTE_ADDR' AND time>'$time'") or die();

Thanks for any help

[coopster]

You should be able to do it all in your SQL, no PHP date/time function work necessary.

DELETE 
 FROM table 
 WHERE 
  ip = '$REMOTE_ADDR' AND 
  time >= CURRENT_TIMESTAMP - INTERVAL 20 SECOND 
;

Note,

time

is a reserved word [dev.mysql.com] in MySQL so you really should avoid using it as a column name. Also, I hope that $REMOTE_ADDR variable isn't because you have PHP register_globals [php.net] on ;)