Page 1 of 1

Delete Row After 30 Mins

Posted: Thu May 07, 2009 5:04 pm
by aeboi80
Allow me to preface this post with the fact that I am not very familiar with sql statements and such, but I have been tasked with completing this job. I have a table called users which contains the following fields:

Code: Select all

id int(11)
username varchar(15)
password varchar(41)
userlevel int(11)
timestamp time
I need to have a row deleted after 30 minutes of it being created. I plan to use a cronjob to accomplish this...but I alas I don't know the sql to do it. Here is the code I have so far:

Code: Select all

 
<?php
$con = mysql_connect("localhost","test","abcd1234");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("test", $con);
 
$currentTime = time();
$deleteTime = time() - (1800);
 
 
mysql_query("DELETE FROM users WHERE userlevel='3' && $deleteTime <= $ currentTime");
 
mysql_close($con);
 
?>
 
As you can see I am not sure what to do because this is obviously not correct. First I realize I am dealing with a MySQL timestamp and trying to use a Unix-Timestamp and not sure how to get around this. I have searched Google and found several variations, but nothing I can piece together to make it work.

Re: Delete Row After 30 Mins

Posted: Thu May 07, 2009 7:25 pm
by ldougherty
The functionality can be done but I think the logic is where you are having issues.

You say you want to delete a row 30 minutes after its been created and you plan to do this via a cron job. How often are you planning on running the cron job?

To effectively do this you'd have to have the cron running consistently which would be a ridiculous amount of queries against the database.

Without knowing exactly what it is you are trying to do here its hard to make suggestions but I'd say a better method would be clear all rows from the table every 30 minutes and run the cron itself every 30 minutes to accomplish the task.

If you can explain in theory what it is you really want to do we may be able to give you a better idea of what to do.

Re: Delete Row After 30 Mins

Posted: Thu May 07, 2009 9:25 pm
by califdon
Every time I read that somebody "needs" to delete records at some arbitrary time, I cringe. That's not the way databases operate. It absolutely DOESN'T MATTER whether a record is or is not in a table at some arbitrary point in time! What you should be doing is storing in the record the time after which the record is no longer "active". Then, if you really want to, you can run a simple routine whenever it's convenient that will delete all records whose "alive" time has passed. Don't ever get tied up in knots with cron jobs for this purpose!

Re: Delete Row After 30 Mins

Posted: Fri May 08, 2009 10:00 am
by pickle
There are MySQL functions FROM_UNIXTIME() and UNIX_TIMESTAMP() that help you convert between UNIX & MySQL timestamps.

Your query will always delete all the entries for users of level 3, as $deleteTime will always be <= $currentTime. What you want is to compare the table column `time` with $deleteTime.

I'd also suggest converting your `time` column to be a datetime format. This will make it aware of when the day changes.