1

So I have a particular field in my database table which I would like to reset to 0 after a fixed period of time, like e.g 1 week. MY language is php. Is there any way to do it? Please help me Okay, so basically I have a column "activation points". These points increase whenever a user does some sort of activity. But I need to reset the points to 0 every week

Nazzu
  • 25
  • 4
  • 1
    What about introducing an expiration date column? Your code only considers a record as valid if the expiration date hasn't passed. – Asaph Apr 17 '16 at 06:13
  • 1
    Can you update lazily? i.e. when you query? – Boris the Spider Apr 17 '16 at 06:15
  • Just a friendly tip, you may want to read over this page: [The How-To-Ask Guide](https://stackoverflow.com/help/how-to-ask) so you can always be sure that your questions are easily answerable and as clear as possible. Be sure to include any efforts you've made to fix the problem you're having, and what happened when you attempted those fixes. Also don't forget to your show code and any error messages! – Matt C Apr 17 '16 at 07:30
  • @Asaph I don't think that will work, since the author specifically said it would be reset, not just ignored. – Matt C Apr 17 '16 at 07:30
  • @Nazzu, You say that you have a table that you would like to reset to 0. Do you mean table column? – Matt C Apr 17 '16 at 07:32
  • Refer to [this post](https://stackoverflow.com/questions/1460066/mysql-question-about-scheduling) – Matt C Apr 17 '16 at 07:37

2 Answers2

0

There is no really nice way to do this with PHP.

You have a few options:

  1. Use MySQL's newly added, built-in, scheduler: Event Scheduler.

  2. Use software from sources other than PHP or MySQL, like cron jobs.

  3. Use a PHP library like PHPScheduler which isn't a true scheduler.

Option 1 is probably your best bet. It is built into MySQL, and you don't have to worry about messing with any other software that you aren't already using.

Option 2 is easy to implement, but does involve using another tool other than just PHP and MySQL. You can learn how to set up cron jobs with this post.

Option 3 is not recommended by me, unless you just absolutely want to use PHP to do this. But you will be limited on the customization of the scheduling. PHPScheduler isn't technically a true scheduler, and you can read why in this post. That post linked is older though, so it will mention that there is no scheduling available in MySQL, but since that post was made, Event Scheduler has been made.

To use Event Scheduler (Option 1), you should refer to this really really good tutorial and if you have any problems, this documentation.

Here's a preview of how easy it is to use Event Scheduler:

CREATE EVENT nazzus_cool_event
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
   INSERT INTO messages(message,created_at)
   VALUES('Nazzus cool event just happened again!',NOW());

This code will create an event called nazzus_cool_event, and it will occur every 1 minute(s) starting at CURRENT_TIMESTAMP (now), and ending an hour from now. It will insert some data into the messages table.

It's very simple but you should definitely have a quick look at this great tutorial so that you can see some more of its great features.

Community
  • 1
  • 1
Matt C
  • 4,470
  • 5
  • 26
  • 44
  • Can you please give me example of option 1? That would be great – Nazzu Apr 18 '16 at 08:12
  • @Nazzu I have added in an example of using Event Scheduler – Matt C Apr 18 '16 at 17:30
  • It worked!!! And it was so easy! Thank you for helping me out.. But one more question, can import/export events from phpmyadmin as we import/export tables? – Nazzu Apr 19 '16 at 18:15
  • I'm really glad to hear it worked!!! You're very welcome! Would you mind marking my answer as correct as it did help answer/solve your post? And upvotes are very much appreciated :) I'll research your import/export problem and let you know if I find a solution for that. – Matt C Apr 19 '16 at 18:20
-1

You can write a simple mysql statement that will updated your field to 0 and put it on cronjob that will run every week.

edit:

Make sql statement that will reset your column, put it in php file and call it on cronjob, you can check it here , you can always use some free online solutions for cronjob like this one

Community
  • 1
  • 1
kunicmarko20
  • 2,095
  • 2
  • 15
  • 25
  • You should at least give the example code of one of the two parts you suggested. Why have you assumed the author is on a Unix based system and that cronjobs are even an option? – Matt C Apr 17 '16 at 07:33
  • @MatthewCliatt example of a mysql statement ? If he doesn't know that then he is on a wrong place, and I don't think you need example for cronjob. About Unix based system, there is a lot of free 3rd party sites that allow cronjob for free, so you don't have to run it on your own server :) – kunicmarko20 Apr 17 '16 at 08:45
  • Of course I didn't expect you to put just any example of a MySQL statement. I was saying you should have at least provided an example of MySQL that sets up a scheduled statement. – Matt C Apr 17 '16 at 08:48
  • @MatthewCliatt Well he can just put sql statement into php file and call it on cronjob. The only thing i didn't provide is how to setup cronjob, but is it so hard to google it? I gave him one of solutions, don't see the need to have this discussion. – kunicmarko20 Apr 17 '16 at 08:51