I have a register of VIP users that make a payment that the registration lasts 1 month.
When the payment is made, the status column of the users_vip table is updated to 1
id|iduser|datapedido|dataconfirm|datafinal |status
1 | 720 |2018-05-03|2018-05-03 |2018-06-03| 1
I would like to create a control, so that when it finishes this period of 1 month that he paid, he changed the status to 2, so he would know that he is registered VIP, but his registration is inactive.
How can I do this?
From a MySQL event-crawled query
An example you can see here, and try to fit into your problem How to create daily event in Mysql 5.6? (A question asked here on the site)
Edited:
To create an event
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
You could create a daily event that will validate if the date of the day is equal to the date datafinal and if it is to change its status to 2
Example
An example of your case would be +/- this
CREATE DEFINER='root'@'localhost' EVENT 'atualiza_clientes_vips'
ON SCHEDULE
EVERY 1 DAY STARTS '2018-05-03 12:00:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT ''
DO BEGIN
update SUATABELA set status = 2
where datafinal >= CURRENT_DATE()
END
As it starts today at 12hr, every day at 12hr it will run again