CODEWITHSUNDEEP
mysql
locnguyen
locnguyen

Reputation: 841

Server to auto reset mysql table values at a specific time

How do I get the server to reset values in a table column.

For example I have a table called "users" with a column called "rebuy". The rebuy column contains "yes" or "no" values for each users. I want the server to reset the rebuy column to "no" every week at Monday 1:00am.

I can do this manually logging into the mysql server 

UPDATE users SET rebuy = 'no'

but I would prefer the server to do this just incase I'm not able to manually.

Upvotes: 0

Views: 214

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

You can have the server reset the flag. Or, you can do basically nothing at all. That is, instead of storing the flag value, you can store when the flag was last set. Then, use a view to add the flag back in, based on this information.

So, define the table so you have:

rebuy_datetime date

Create a view:

create view v_table as
    select t.*,
           (case when rebuy_datetime >= curdate() - interval weekday(curdate()) day + interval 1 hour 
                 then 'yes'
                 else 'no'
            end)
    from table t;

Then, when you query the table, the flag is set automagically.

Upvotes: 0

DasSaffe
DasSaffe

Reputation: 2198

The simple solution would be to add a cron-job. Here is a pretty nice tutorial for it http://www.cyberciti.biz/faq/how-do-i-add-jobs-to-cron-under-linux-or-unix-oses/

You can achieve exactly what you are looking for. Set the day and time and the cronjob will do it automatically

It is hard to give you an code-example, since it may differ on different OS.

Your goal is to get something like:

connect to your server and type: crontab -e

0 1 * * 1 /path/to/your/phpscript (not tested, but should be fine)

Upvotes: 2

Related Questions