I have a users
table and two columns are having a relationship. The value of the first column is the monthly deductions of each user.
I want the data in the first column to keep auto increasing itself in the other column on a monthly basis. Example: if user A
has $5
as their monthly deduction and has $10
as their total_deduction in February, by march the total deduction should auto increase to $15.
I could use event in phpmyadmin sql I guess.
Advertisement
Answer
Absolutely, you can schedule an event
for monthly deduction. Before creating an event
make sure in your mysql
you set event_scheduler
is ON
which in default is OFF
.
SET GLOBAL event_scheduler = ON;
Event: you can add fixed amount or user-wise deduction from another column.
1) For fixed amount:
CREATE EVENT user_monthly_deduction ON SCHEDULE EVERY '1' MONTH DO BEGIN UPDATE test.users SET total_deductions = total_deductions + $5; END$$
2) From another column:
CREATE EVENT user_monthly_deduction ON SCHEDULE EVERY '1' MONTH DO BEGIN UPDATE test.users SET total_deductions = total_deductions + monthly_deduction; END$$
For more details about events-configuration
and create_event
you can check this official document. mysql-event-configuration
and create-event