How to change MYSQL database when 1 month registration date expires

Question

I have a register of VIP users who make a payment that registration lasts 1 month.

When 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 he finishes this period of 1 month that he paid, change the status to 2, so you would know that he is registered VIP, but his registration is inactive.

How can I do that?

Answer 1

From a query about creating events in Mysql

An example you can see here, and try to fit into your problem How to create a daily event in Mysql 5.6? (A question asked here on the website)

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 whether the date of the day is equal to the date datafinal and if you change your status to 2

Example

An example of your case would be +/- that

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 he starts today at 12hr, every day at 12hr he will run again