I have a user table where i have a balance field! A Reseller can edit a user thorugh a form and may set balance to Yes Or No. If He Sets the balance to yes then after 30 days i want to reset the balance of only that user to no. Each user is mapped by a unique field called key. So once the balance is set to yes trigger should count 30 days for that specific user and reset it back to No after 30 days
i am using PHP Codeigniter.
About setting a cron job refer to this page: http://www.thesitewizard.com/general/set-cron-job.shtmlenter link description here
Apart form that, about the thing you want to do: First you should create a controller class for cron jobs. This is actually a page on your website, that, when accessed, executes the code you want.
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class cronjobs extends CI_Controller {
public function __Construct(){
}
public function index(){echo 'Nothing to see here.'}
public function reset_resellers(){
$this->load->model('your_model');
echo $this->your_model->reset_resellers() . "resellers were reset.";
}
}
So if your cron job is set to access this page once every 30 days, you get to run the script just as many times. About the actual script, this is harder to answer. But we'll try: You must have a model that updates the database, more or less like this:
update reseller.balance = 'no' where balance = 'yes' and balance_set_date = today - 30 days
So you must have a column in your resellers table where you keep balance_set_date but you might call it differently, and suppose you keep that in date format, and not in timestamp format, let's try to create that model:
function reset_resellers(){
$this->db->where('balance', 'yes');
$this->db->where('balance_set_date <= CURDATE() - 30', '', false);
$query = $this->db->update('resellers');
return $query->num_rows();
}
And finally, if you want to check if your job would do what you want, just access the url www.example.com/cronjobs/reset_resellers
According to my understanding, you need to update the values in the db 30 days after the change. You don't want to do so in the end of each month for example.
What you can do is something similar to this : MySQL trigger: Update when reaching a certain datetime.
