php delete script

Question

I have a field in a sql database called time_stamp that timestamps when the record was added to the database. I have another field called 'TTL', which will have either 1, 2, or 3 stored in it (each number within this field represents a day). Can I have a php script where the deletion of the record is determined by the 'TTL' field?

For instance a record is added at noon today, with a 'TTL' of 2, can I have a script that deletes that same record at noon two days from now?

What would be the best way to do this, and do I really need both fields to execute this?

Not really, I was going to give it a stab but wanted to get a different perspective of how it should be done. I am kinda stumped on where to start. — need2nobasis
– need2nobasis, Commented Jul 12, 2012 at 19:27

Daniel Li · Accepted Answer · 2012-07-12 21:08:45Z

1

Your best method for running this script is attaching it to cron and having it run on a given interval.

Then, in order to program the record deletion, you'll need to run the following query:

DELETE FROM records WHERE UNIX_TIMESTAMP(time_stamp) + TTL * 86400 <= CURRENT_TIMESTAMP()

time_stamp is your record creation value - let's say it's equal to 1000000. 86400 is the amount of seconds in a day. TTL is the amount of days after time_stamp that you want the file deleted. So if TTL is 2 and 1000000 was the timestamp for July 10th, we are basically comparing:

1000000 + 86400 * 2 <= CURRENT_TIMESTAMP

Now, let's say that today is July 12th. This means it's 2 days (86400 * 2 seconds) after July 10th. Thus,

CURRENT_TIMESTAMP = July 10th timestamp + 2 days worth of seconds
                  = 1000000 + 86400 * 2

Which implies CURRENT_TIMESTAMP >= 1000000 + 86400 * 2 for all points in time during and after July 12th, thus deleting the record.

This will delete the records daily for you while your server is running. Enjoy and good luck!

edited Jul 12, 2012 at 21:08

answered Jul 12, 2012 at 19:21

Daniel Li

15.6k6 gold badges45 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

need2nobasis Over a year ago

This will delete the daily records but what if the user chooses for that record to delete after the second day or after the third instead of the first?

Daniel Li Over a year ago

No, this will delete only the records that should be deleted on a given day. 86400 represents the unix time for a full day and acts as a condition for deletion (will only delete on the 2nd day if TTL = 2, 3rd day if TTL = 3, etc.)

need2nobasis Over a year ago

I get it now thank you, but the UNIX_TIMESTAMP reference. Will that work if I am using current_timestamp with attributes of 'on update current_timestamp'?

Daniel Li Over a year ago

Remove on update current_timestamp, it will update your time_stamp value and ruin your delete task completely. If you felt that I was of assistance, an upvote and accepted answer would be cool :) Enjoy!

need2nobasis Over a year ago

okay, I might be a little more confused than I originally thought. Correct me where I'm lost, 86400 * 1 + timestamp will never be less than the current_timestamp correct? I am not sure how this works.

|

Collectives™ on Stack Overflow

php delete script

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related