Autoresponder: Send in 1 Minute After Subscription SQL Statement

Question

I've creating autoresponder system for subscribing thank you messages with dynamic time condutions.

General settings has time feature like after 1 minute, 1 day, 1 week etc. When a guest subscribe on my list, cron based autoresponder should be send thank you messages with selected time conditions. But my sql query does not work properly. I've try many queries but there no success right now. Here is my build;

subscribers table

ID   mail              add_date
1    [email protected]   2015-06-16 22:11:20
2    [email protected]   2015-06-16 22:19:30

Current time: 2015-06-16 22:20:30

SQL Query:

SELECT 
        S.* 
FROM 
        subscribers AS S
        LEFT JOIN tasks AS T ON (T.CID=2 AND S.subscriber_mail=T.subscriber_mail)
        LEFT JOIN unsubscribes AS U ON (U.CID=2 AND S.subscriber_mail=U.subscriber_mail)
WHERE
        (T.subscriber_mail IS NULL)
        AND (U.subscriber_mail IS NULL)


 AND (S.add_date > date_sub('2015-06-16 22:20:30', interval 1 MINUTE)) LIMIT 5000

It successfully sent, but if I create new conditions for "after 1 day" it does not work properly, it sent same message after first query, it do not wait 1 day for that.

in a nutshell;

I want to send 3 mail after subscription, 1 minute, 1 day, 1 month later.

Thank you!

Answer 1

Maybe this solve you problem:

SELECT * FROM 
    subscribers
WHERE
    add_date >= DATE_SUB(CURDATE(),INTERVAL 1 DAY)

if you use NOW() you will always have the minutes / seconds in query that you don't need (if your difference is >= one day).