Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Automatic TimeStamp Actions

  1. #1
    Join Date
    Aug 2011
    Posts
    32
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default Automatic TimeStamp Actions

    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!

  2. #2
    Join Date
    Mar 2007
    Location
    New York, NY
    Posts
    557
    Thanks
    8
    Thanked 66 Times in 66 Posts

    Default

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

  3. #3
    Join Date
    Aug 2011
    Posts
    32
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

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

  4. #4
    Join Date
    Mar 2007
    Location
    New York, NY
    Posts
    557
    Thanks
    8
    Thanked 66 Times in 66 Posts

    Default

    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:
    PHP Code:
    $time time();

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

  5. #5
    Join Date
    Aug 2011
    Posts
    32
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    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.

  6. #6
    Join Date
    Mar 2007
    Location
    New York, NY
    Posts
    557
    Thanks
    8
    Thanked 66 Times in 66 Posts

    Default

    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 Code:
    <?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
    - Josh

  7. #7
    Join Date
    Aug 2011
    Posts
    32
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    Quote Originally Posted by JShor View Post
    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 Code:
    <?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?

  8. #8
    Join Date
    Mar 2007
    Location
    New York, NY
    Posts
    557
    Thanks
    8
    Thanked 66 Times in 66 Posts

    Default

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

  9. #9
    Join Date
    Aug 2011
    Posts
    32
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

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

  10. #10
    Join Date
    Mar 2007
    Location
    New York, NY
    Posts
    557
    Thanks
    8
    Thanked 66 Times in 66 Posts

    Default

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

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •