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    mail1@gmail.com   2015-06-16 22:11:20
2    mail2@gmail.com   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!

Think about switch the direction: create 3 mails in a table with date_add(CURRDATE(),INTERVAL 1 DAY) etc. and than search for unsend emails <= time() / now(). — codeneuss, Jun 17 '15 at 20:05
@v.eigler there is no reason to bloat the database like that — cmorrissey, Jun 17 '15 at 20:08
@cmorrissey the reason can be that someone want to know if a mail has been send. — codeneuss, Jun 17 '15 at 20:11
@v.eigler if that is the case just add 3 boolean or date columns to the table to mark that or set when it was sent — cmorrissey, Jun 17 '15 at 20:12
@cmorrissey well, I just said "think about". But if you have a better solution answer the question ;) — codeneuss, Jun 17 '15 at 20:13

score 0 · Answer 1 · answered Jun 17 '15 at 20:21

0

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).

answered Jun 17 '15 at 20:21

codeneuss

905
4
12

Autoresponder: Send in 1 Minute After Subscription SQL Statement

1 Answers1