karmacomposer Posted June 16, 2021 Share Posted June 16, 2021 How would I reset a counter every 7 days? I already have a table with a date created field. I just need to reset a counter every 7 days - say Monday morning at 1am or whatever time sets the new day. It has to be after a week, every week. The other counter can go up to 10 but must reset to 0 after 7 days. This is a voting system where the logged in person can vote up to 10 times in a week, but no more than 10 times. I have the votecount stored in a field as well as totalvotes. The votecount is the counter that goes up to 10 for each logged user. The username of the voter is also stored to tie them to their user account. Everything is done and working except for this. I would rather do this in php if possible, but I am open to mysql queries if there is no other way. Thanks for any help. Mike Quote Link to comment https://forums.phpfreaks.com/topic/312919-reset-a-counter-weekly-in-phpmysql/ Share on other sites More sharing options...
Barand Posted June 16, 2021 Share Posted June 16, 2021 (edited) There is a very easy way to do this using a MySql myisam table. Store each vote in a table like this... CREATE TABLE `vote` ( `username` varchar(50) NOT NULL, `vote_year` year(4) NOT NULL, `week_num` tinyint(4) NOT NULL, `vote_count` int(11) NOT NULL AUTO_INCREMENT, `time_voted` datetime DEFAULT CURRENT_TIMESTAMP, `voted_for` int(11) DEFAULT NULL COMMENT 'Who/whatever was voted for', PRIMARY KEY (`username`,`vote_year`,`week_num`,`vote_count`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Note the last column of the primary key is the auto-increment column "vote_count". This will make the vote count start again at 1 for each username each week whe you add votes. mysql> INSERT INTO vote (username, vote_year, week_num, voted_for) VALUES -> ('user1', year(curdate()), weekofyear(curdate()), 1), -> ('user1', year(curdate()), weekofyear(curdate()), 20), -> ('user1', year(curdate()), weekofyear(curdate()), 5), -> ('user2', year(curdate()), weekofyear(curdate()), 5), -> ('user2', year(curdate()), weekofyear(curdate()), 1), -> ('user2', year(curdate()), weekofyear(curdate()), 3); Query OK, 6 rows affected (0.00 sec) mysql> select * from vote; +----------+-----------+----------+------------+---------------------+-----------+ | username | vote_year | week_num | vote_count | time_voted | voted_for | +----------+-----------+----------+------------+---------------------+-----------+ | user1 | 2021 | 24 | 1 | 2021-06-16 17:37:56 | 1 | | user1 | 2021 | 24 | 2 | 2021-06-16 17:37:56 | 20 | | user1 | 2021 | 24 | 3 | 2021-06-16 17:37:56 | 5 | | user2 | 2021 | 24 | 1 | 2021-06-16 17:37:56 | 5 | | user2 | 2021 | 24 | 2 | 2021-06-16 17:37:56 | 1 | | user2 | 2021 | 24 | 3 | 2021-06-16 17:37:56 | 3 | +----------+-----------+----------+------------+---------------------+-----------+ 6 rows in set (0.00 sec) To do the insert in PHP $stmt = $pdo->prepare("INSERT INTO vote (username, vote_year, week_num, voted_for) VALUES ( ?, year(curdate()), weekofyear(curdate()), ? ) "); $stmt->execute( [ $username, $votedFor ] ); Now it doesn't matter how many times a user votes. When you come to counting the votes for each "voted_for" just ignore all records whose vote_count is > 10 SELECT voted_for , count(*) as votes FROM vote WHERE vote_count <= 10 GROUP BY voted_for ORDER BY votes DESC Edited June 16, 2021 by Barand 2 Quote Link to comment https://forums.phpfreaks.com/topic/312919-reset-a-counter-weekly-in-phpmysql/#findComment-1587273 Share on other sites More sharing options...
requinix Posted June 16, 2021 Share Posted June 16, 2021 An alternative is to use a similar approach (of scanning the table for existing records) but at the time of the vote submission to prevent another vote from being added if there are already 10 from that user. 1 Quote Link to comment https://forums.phpfreaks.com/topic/312919-reset-a-counter-weekly-in-phpmysql/#findComment-1587275 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.