Hello and welcome to our community! Is this your first visit?
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
    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
    Quakertown PA USA
    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
    Thanked 4,744 Times in 4,706 Posts
    Yeah, you could do this very nicely in a Stored Proc:
    delimiter //
    CREATE PROCEDURE updateLogonCount( )
        UPDATE logonCount SET count = count + 1 WHERE countDate = CURDATE();
        IF ROW_COUNT() = 0 THEN
            INSERT INTO logonCount ( count, countDate ) VALUES( 1, CURDATE( ) );
        END IF;
    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 03: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