Automatic TimeStamp Actions

[Techykid3]

Okay, here is what I want to do, I have a system where if a person votes for something, it will add their name, and what they voted for, into a MySQL table (votes), I want to automatically, after 24 hours, STARTING AT THE TIME THEY VOTED, to automatically delete their row containing their name, if it has been 24 hours since their vote.

Any help would be appriciated. And yes, if I need to, I can have a scheduled task open a webpage with the script (to check and delete).

Thanks!

[JShor]

Well, you could do this using a cron job that runs every few minutes or so, which will delete any posts with a timestamp which is past 24 hours ago. This should be very easy to accomplish if you're storing everything in UNIX time.

Please include your problematic code so that we can understand what your problem is.

[Techykid3]

What I'm using is the MySQL datetime column, but the problem is, I don't know how to even insert the datetime into the database. Could I get some codes? What I all in all need is, 1, the php code that will delete the posts that are older than a day old, and the code that I put in the insert function. I got all the rest, all I need is those codes.

By the way, I seem to be storing the times in 0000-00-00 00:00:00 format... (says the database).

[JShor]

Change the field type of the column that you're storing the timestamp in to BIGINT, because the timestamp feature stores the time of insertion, not the UNIX time.

When you're inserting, use time(), which will return the current timestamp (number of seconds since January 1st, 1970 at midnight Greenwich time).

Example:

$time = time();

mysql_query("INSERT INTO `table` (timestamp) VALUES('$time')")or die(mysql_error()); 

[Techykid3]

Okay, now I got that figured out before you replied :P. I got it to insert correctly, but I need a script, that deletes rows when they are older than 24 hours, which means allowing each row, to be in the table for 24 hours, as they could come in at any time, any second.

Here is how the time is:

date('Y-m-d H:i:s');
Outputs this:
2011-08-26 12:59:48

So now, I just need a script wrote that tells it, whenever a row's date, gets further than 24 hours ago, delete that row.

[JShor]

UNIX timestamps have an inverse relation to the ratio of time decrease to number increase. What you want to do is use strtotime() to subtract one day from the current time, and delete from the table WHERE timestamp is less than the subtracted time.

For example:

<?php

$subtime = strtotime("-1 day");

mysql_query("DELETE FROM `table` WHERE time < $subtime") or die(mysql_error());

?>

Put that in a file and have a cron job executed for how ever often you want. Keep in mind, the more frequently you have it execute, the more resources you use up. And the less frequently you have it execute, the less accurate your results will be.

You can also use an event scheduler, which is like a cron job except more accurate and less resource-hungry:
http://dev.mysql.com/doc/refman/5.1/en/events.html

[Techykid3]

UNIX timestamps have an inverse relation to the ratio of time decrease to number increase. What you want to do is use strtotime() to subtract one day from the current time, and delete from the table WHERE timestamp is less than the subtracted time.

For example:

<?php

$subtime = strtotime("-1 day");

mysql_query("DELETE FROM `table` WHERE time < $subtime") or die(mysql_error());

?>

Put that in a file and have a cron job executed for how ever often you want. Keep in mind, the more frequently you have it execute, the more resources you use up. And the less frequently you have it execute, the less accurate your results will be.

You can also use an event scheduler, which is like a cron job except more accurate and less resource-hungry:
http://dev.mysql.com/doc/refman/5.1/en/events.html

I'm having it run every midnight, so it has it ready for the next day. I should probably make it run at 12:05 AM. Will this be deleting them all SEPERATE, by each user's timestamp? I need it to go by minute, so people don't cheat their way to vote 2 times in an intersection, 24 hours from each person's timestamp, it needs to be deleted.
Is that EXACTLY what the script you made does?

[JShor]

No. As I mentioned before, the script will only perform deletes whenever the script executes. Logically, if it only executes once a day, then there will be results that exceed 24 hours.

For it to be perfectly accurate, you would need to have it execute once every minute.

[Techykid3]

How would I do that, without bearing down my computer?

[JShor]

Do you mean your server? I'm assuming you're running a cron job on Apache. If you want to delete records, this is the only way to do it short of configuring MySQL events (as I mentioned before).

Personally, I would do this a much more elegant way. I'm not clear on how your system works, but I would set it up so that when a person tries to vote, their vote will be accepted again IF the timestamp is after the 24 hour time period, and not allow them if it's during the period.

It's a much faster, more elegant and least resource-hungry option for you.