mysql and 30 days

Question

i'm working on a site that handles free subscriptions and i will like to now how to cancel their subscriptions after 30 days inactivity, i know that this has to be done with cron-jobs but i have no idea on how to count 30 after the last time the user logged in?

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 DATE_ADD function

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

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

You can do it in 1 UPDATE statement

UPDATE yourTable SET active = 0 WHERE validity_date < CURRENT_DATE

And when they subscribe, you receive payment

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

Answer 3

You can do a query that will get you that. In fact, the MySQL documentation has your example exactly. Do a quick search for "30 days" on this page: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html.

Answer 4

And if one does not want to use cron and using a MySQL 5.1 or greater one can use event. http://dev.mysql.com/doc/refman/5.1/en/create-event.html