Jump to content

Reset a counter weekly in php/mysql


Recommended Posts

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

Link to comment
Share on other sites

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 by Barand
  • Great Answer 2
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.