Home > Net >  Reset to default value depending with the year
Reset to default value depending with the year

Time:04-21

I want to automatically reset the value in hours_remaining to its default value according to year. I also want the year to auto-incremented. Can anyone give me some advise or help on how to implement this? By the way I'm new to using Php MySql.

From this:

 employee_id |  year  | default_value | hours_remaining |
 ========================================================
       1     |  2022  |      80       |        67       |  <---- When current year

To this when another year:

 employee_id |  year  | default_value | hours_remaining |
 ========================================================
       1     |  2023  |      80       |        80       |  <---- When year is changed

CodePudding user response:

This is the perfect use of MySQL's EVENT, which can perform periodic jobs. Just make sure the event scheduler is toggled on. Then create a event and watch it happens:

delimiter //
set global event_scheduler=on //
drop event if exists newyear_update //

create event newyear_update on schedule every 1 year starts '2023-01-01' do 
BEGIN
update testtb set year=year 1,hours_remaining=default_value ;
END//

Of course you are not going to wait another 8 months to find out. To test it, you can just change the time to every 60 second starts now()

  • Related