[RESOLVED] Using PHP to create recurring emails

mrw2020

Hi, I'm trying to write a script that would check a database for renewal dates for a certification site and send an email when the renewal date is due.

For example miss A took her first aid certificate 15 June 2011
I want to check the database and then send out a reminder email 12 June 2012

Is there already a script out there that can do this or could someone point me in the right directions for creating one?

I will eventually add a cron job to automate the process. Any help would be greatly appreciated.

bradgrafelman

Which part do you need help with? Sounds like you simply need to SELECT the appropriate rows, loop through the result set, and send out the e-mail for each row. Add a cron job to repeat this process however often you'd like and then you're done.

planetphp

in mysql you could use the DATE_SUB feature eg:

SELECT * FROM table WHERE renewal = DATE_SUB(CURDATE(), INTERVAL 3 DAY);

Loop through the results and fire off your email.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-sub

bradgrafelman

You can also take advantage of the fact that MySQL has "overloaded" the addition and subtraction operators to handle DATE columns correctly, so if the desired criteria is to look for rows where today is equal to (date certified + 1 year - 3 days, e.g. 3 days before the 1 year anniversary) then you could simply write:

CURDATE() = dateCol + INTERVAL 1 YEAR - INTERVAL 3 DAY

mrw2020

Thank you both so much for your reply, that's exactly what I wanted - I just couldn't seem to see the wood for the trees and I'm not very familiar with the date functions.

So for example if I had a table called students with a column called certdate

the code would be:


"SELECT certdate, email FROM students WHERE CURDATE() = certdate + 
INTERVAL 1 YEAR - INTERVAL 3 DAY"