CODEWITHSUNDEEP
mysqlcodeigniter
Rajan
Rajan

Reputation: 2425

How to create a trigger that resets values to zero after 30 days?

I have a table 'users'. In that table there is a field balance whose datatype is ENUM('Yes','No'). Now I want to create a trigger that is fired when the balance is set to "Yes". Also I want to reset the value of Balance to NO after 30 days.

I am using PHP Codeigniter. And I have a reseller table who has its own users. So Each reseller has some users.

So when a reseller sets the balance of user to yes the trigger should be fired and after 30 days should set user balance to NO

Also the balance of each user should be Reset uniquely. I mean one uses's balance is set to yes today and user-two balance is set to Yes tomorrow . So how will trigger know when to fire for that each specific user?

Upvotes: 1

Views: 232

Answers (2)

jagad89
jagad89

Reputation: 2643

In order to track date you need to keep a column on which user updated_to_yes_date. When ever user update that enum column to "Yes" you should set current date to updated_to_yes_date.

Now lets code the controller for cron job.

class Cronjob extends CI_Controller {

  function index() {
    // Get Date before 30 days.
    $today = new DateTime();
    $dateBefore30days = $today->modify('-30 days');

    $this->db->update('users',array('enumcolumn'=>'No'), array('enumcolumn'=>'Yes', 'updated_to_yes_date'=>date_format($dateBefore30days , 'Y-m-d'));
  }
}

I have not tested above controller but it will help you.

Now its time to set up cron job. You need to run this cron job every day. To know how to setup cron job on cPanel CLICK HERE

Now we need to execute controller method from command line. For that go to codeignitor Running via the CLI document.

In our case it will be like

$ php index.php cronjob index

In many case we need to provide full path to index.php like

$ php /path/to/webroot/index.php cronjob index

Upvotes: 2

mrsrinivas
mrsrinivas

Reputation: 35404

My solution is

  • Maintain a column like updatedon.
  • Write a stored procedure to set balance to NO when updatedon is 30 days and balance is YES(according to your critaria)
  • Run a Job daily to invoke the stored procedure(It will set only 30 days back updated records)

Upvotes: 2

Related Questions