When a member's expiration date is reached, I will need a system of flagging the member record as 'expired'. Easy enough. Then I will fire off an email to the member letting them know their membership is expired.
What I want to do is send one email on their expiration date, another when they are 10 days expired, another when they're 20 days, and a final notice when they're 30 days.
I don't want to accidentally email the same people every day, so perhaps if I set the exp date field to 'pending 1' for 1 day expired, and 'pending 2' for two days expired, I could keep them separate and only email the ones I need to.
I know I'll need to update the records using a scheduler, that's fine.
Not sure what information I should be updating the records with.
It depends on how many times a day you run the script. If it's just once a day it's pretty easy. Just store the data their account expired in the database (let's assume this field is called
expired) and check if DATEDIFF(CURRENT_DATE, expired) = 10 (or indeed 20 or 30) and if so, send the email.
If you run the script multiple times a day you'd need to add another field indicating when you sent the last email and don't send an email if that field holds today's date
Great answer. And simple to test. Thanks very much!