Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
Thread: Sql Date Update
02-22-2009, 12:09 AM #1
- 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.
02-22-2009, 02:28 AM #2
- 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).
02-22-2009, 03:27 AM #3
Yeah, you could do this very nicely in a Stored Proc:
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.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 ;
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.