02-22-2009, 12:09 AM
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
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
Yeah, you could do this very nicely in a Stored Proc:
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( ) );
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.