Writing a cron to bill a monthly subscription

[sneakyimp]

So I ran into an interesting problem trying to write a script that renews subscriptions.

I'm writing a site wherein users can pay monthly to gain access to certain features. I was trying to scheme up the cron job that would renew these subscriptions and, using Authorize.net's CIM profiles, would automagically bill any accounts that have not paid in a month.

My first impulse was to query for accounts whose last payment was over a month ago:

SELECT * FROM accounts WHERE last_payment_date <= DATE_SUB(NOW(), INTERVAL 1 MONTH);

The problem with this code is for accounts last paid on January 30 or 31. When we get to February 29th, everything's fine, but then Mar 1 rolls around and we'll have a few days worth of subscriptions to renew.

I guess technically speaking that Mar 1 is "over a month" past Jan 30/31, but just thought it was interesting. Over time this would have the effect of moving all subscription renewal dates off the 29, 30, or 31 of a given month into the first of the month.

Any thoughts about a cron job to automatically charge for subscription renewal are much appreciated.

[Derokorian]

I don't know if you solved this yet sneaky or not. But my first thought would be to set the INTERVAL to 30 DAY instead of 1 MONTH because always getting charged on day X of a month means that 7 payments would represent 31 days, 4 would represent 30 days and 1 would represent 28 or 29 days depending on the year. If you prefer the on day X of a month then you have to change your logic such that subscriptions started on say the 31st of January always renew the LAST day of the month. On the 30th, would either be the last day or the 30th which ever is sooner (IE in January it would be the 30th because that's earlier than 31, but in February it would be 28/29 because that would be earlier than 30). In my subscriptions I've always used the 30day rule. Hope that helps.

[sneakyimp]

Excellent suggestion. I see that the logic of maintaining a particular day of the month is not particularly convoluted.