Mysql and 30 days

Question

I’m working on a site that processes free subscriptions, and now I will like how to cancel subscriptions after 30 days of inactivity, I know that this needs to be done with cron jobs, but I have no idea how 30 is after the last user logged in system?

Answer 1

SELECT user_id FROM users WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) > last_logged_in_date

Answer 2

You need to use mysql function DATE_ADD

SELECT DATE_ADD('YOUR DATE', INTERVAL 30 day)

See the mysql manual for more information http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add .

You can do this in 1 UPDATE statement

UPDATE yourTable SET active = 0 WHERE validity_date < CURRENT_DATE

And when they sign up, you get paid

UPDATE yourTable SET active = 1, validity_date = DATE_ADD(CURRENT_DATE, INTERVAL 1 MONTH) WHERE id = 'somekind of id'

Answer 3

And if you do not want to use cron and using MySQL 5.1 or higher, you can use the event. http://dev.mysql.com/doc/refman/5.1/en/create-event.html

Answer 4

You can make a request to help you. In fact, the MySQL documentation has your example. Do a quick “30 days” search on this page: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html .