Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: Sql Date Update

  1. #1
    New Coder
    Join Date
    Sep 2008
    Posts
    94
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Sql Date Update

    hey i've been using mysql for a while now. I was wondering is there a way that everyday automatically at midnight it would insert a new date for the new records to start adding.

    I am trying to add number of people visiting. I can do all that. But i need for the database to insert a new row every new day.

  • #2
    Senior Coder
    Join Date
    Apr 2007
    Location
    Quakertown PA USA
    Posts
    1,028
    Thanks
    1
    Thanked 125 Times in 123 Posts
    You could do this with a cron job and php.

    Although, I suspect this isn't necessary. When someone visits, simply check to see if a record for today has been created - if so, add to the total (or whatever you're doing); if not, add the create (and do whatever else).

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,857
    Thanks
    78
    Thanked 4,417 Times in 4,382 Posts
    Yeah, you could do this very nicely in a Stored Proc:
    Code:
    delimiter //
    CREATE PROCEDURE updateLogonCount( )
    BEGIN
        UPDATE logonCount SET count = count + 1 WHERE countDate = CURDATE();
        IF ROW_COUNT() = 0 THEN
            INSERT INTO logonCount ( count, countDate ) VALUES( 1, CURDATE( ) );
        END IF;
    END;
    
    delimiter ;
    See the logic?? If the UPDATE query doesn't affect any rows, it must be because there is not yet a row for the given countDate, so you then turn around and insert it.

    This is much more efficient than first checking to see if the row exists, because this code will only do the "extra" work of a subsequent INSERT one time per new date.

    Off the top of my head, but feels right.
    Last edited by Old Pedant; 02-22-2009 at 02:41 AM.


  •  

    Tags for this Thread

    Posting Permissions

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