将用户帐户设置为在给定时间后过期[关闭]

Question

The users table in a database has the following fields(among others):

• date created (the date when the user has joined)
• confirmed (set to 1 if the user has confirmed his email)
• approved (set to 1 if the user has beed approved by the admin)

What I would like to do is to create another row, called "period" which will be updated to 1 if the user wants to join for 1 month, 2 if he wants to join for 6 months, and 3 if he wants to join for 1 year

I wonder if it is possible to somehow automatically set the approved from 1 to 0 after the chosen period has expired. I am just asking for some ideas... I hope that after reading your opinions I might come with the best solution in my particular case....

how would you aproach such an issue?

Answer 1

The best way to do this is with a view. You can compare the current time to the end time for the subscription:

create view v_users as
    select u.*,
           (now() < (datecreated + interval (case when period = 1 then 1
                                                  when period = 2 then 6
                                                  when period = 3 then 12
                                              end) month
                    )
           ) as approved
    from users u;