SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Resetting an MySQL Table

    Hey guys I have a question.

    Is there a way to reset a MySQL at a certain time? I have a table that will take votes. I want to have all the votes cleared from the table at midnight each day. Is there some way to do that? Is there a way to check the time on the server and then reset the table when it hits midnight? Thanks.

    Brandon

  2. #2
    Counter-Striker
    Join Date
    Sep 2005
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,
    To emtpy it of rows you do TRUNCATE *table name* in the mysql query
    Example:
    PHP Code:
    $sql "TRUNCATE `Votes`";
    $query = @mysql_query($sql); 

  3. #3
    SitePoint Addict
    Join Date
    Feb 2006
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, that works. However, what about trunctating it at a certain time?

  4. #4
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    New Jersey, USA
    Posts
    567
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There's no way to truncate at a certain time.

    You can use the unix 'cron' facility to run an arbitrary script at midnight. Then you'd write a script for truncating the table.

    Alternatively, you could code your PHP so that it queries only for votes from today at 00:00:00 forward. That way your vote-counting would be accurate. Then you could put in another bit that checked for votes BEFORE today at 00:00:00 and did the truncate. A special case that would get run with the first vote of a new day:
    Code:
    $midnight = timestamp(date(today) . " 00:00:00");
    $db->execute(DELETE FROM votes WHERE vote_time < $midnight);
    $db->query(SELECT * FROM votes WHERE vote_time >= $midnight);
    =Austin
    Austin Hastings - Principal Consultant - Longacre, Inc.

    Anything you can do, you can do better.

  5. #5
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  6. #6
    SitePoint Evangelist LemoNade's Avatar
    Join Date
    Aug 2005
    Location
    Southampton, UK
    Posts
    500
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry to butt-in, but does using truncate also reset the AUTO_NUMBER?

    Cheers,

    -Will
    My portfolio: lemonadeX.net
    XHTML/CSS/PHP coder for hire

  7. #7
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    New Jersey, USA
    Posts
    567
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LN,

    Yes and no.

    Shutting down resets the autonumber to "whatever I can find by scanning the table, plus one."

    Truncate obviously reduces what can be found by scanning the table.

    It's a two-part thing:

    1. Shutting down isn't a problem, necessarily.

    2. Deleting records isn't a problem, necessarily.

    But (3=1+2) Deleting records then shutting down is a problem.

    And it's only a problem if you genuinely need the autoincrement behavior to generate "eternally unique" numbers, or if you are trying to record "number of items created, ever."

    It's not very satisfying, but both of those arguments conflict with the "unique key" behavior guaranteed by auto_increment. Auto_increment essentially only guarantees local uniqueness -- this number is unique in the table as presently constituted.

    =Austin
    Austin Hastings - Principal Consultant - Longacre, Inc.

    Anything you can do, you can do better.


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
  •