CODEWITHSUNDEEP
phpmysql
Albert MN.
Albert MN.

Reputation: 730

Reset MySQL value every day with PHP

I am trying to make a PHP hit counter that saves the hits in to MySQL. It would be best if I could somehow get a count for every day, but so far I haven't figured how to do that without it being too complicated. So, for now (unless someone here has a solution for above ^) I want to reset the "todays hits" in MySQL every day, at 00:00. I could not seem to find a way of scheduling this to be executed automaticly every day at 00:00 (GMT 0, UK time).

Upvotes: 0

Views: 2183

Answers (2)

Jeff
Jeff

Reputation: 6953

Giancarlo answers your question absolutely correct,
but I'd suggest to have a different approach:

have a "hits"-table with the fields 

hitsDate (date) | hits (int)
----------------------------------
2015-01-06    | 329
2015-01-05    | 0

Everytime you want to add a hit (on login maybe? or on session start?) compare with the current date:

Update hits_table set hits=hits+1 where hitsDate=curdate()

If this returns an error or false, then there is no row to be updated, so we insert a new one with the current date and the first hit:

Insert into hits_table (hitsDate, hits) values (curdate(), 1)

Now you can view the hits for any day you want, can make nice charts, ...

Upvotes: 1

Giancarlo Ventura Granados
Giancarlo Ventura Granados

Reputation: 1180

You have two solutions:

Use the event scheduler for mysql

CREATE EVENT 
ON SCHEDULE EVERY 1 DAY
STARTS '2015-01-07 00:00:00'
DO
[SQL for reset the value here];

Other solution is use an PHP script and execute it with cron

#!/usr/bin/env php
<?php
[PHP script for reset the value here]

Then, add it to the cron jobs

 0 22 * * * /usr/bin/php -f /usr/local/bin/my-daily-script.php &> /dev/null

(You can change 22 for other hour value)

Upvotes: 3

Related Questions