mysql/php emailer issue

Question

I created an emailer that checks when was the last time a user logged in, if the user didnt log in for 10 days it sends him/her an email. Now I want the system to check if that same user does it twice, and email them a different email. table 1 users:

id - name - loggedindate
0    john      7/11/2012
1    mark      7/6/2012
2    johnny    7/6/2012
3    bruce     7/2/2012

table 2 mailer fields are as follow:

id - days -emailtosend - numberoftimeseventhappens
0     4      hello                  1
1     4      blabla                 2
2     6      example                1
3     3      to                     1
4     4      demonstrate            1

so here for example mark and johnny didnt log in for 4days. the first time that event happens it sends them the email "hello" the second time the event happens the emailer needs to send them "blabla" so lets say mark doesn't log in for 4days,first email will go then he logs in on day 5, then after another 4days he doesnt log in, I want to send him the second email that would be ID=2 on table 2 the file runs on a cron and checks every 2 hours any ideas on how to do it ? I just need the way to do it not the actual code I can write it myself, I ve been pulling my hair over this one.

Marc B · Accepted Answer · 2012-07-10 23:48:41Z

1

Add a new field to the user table that keeps track of the last type of email that was sent to them. Next time you have to send an email, you send "last email + 1"

answered Jul 10, 2012 at 23:48

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cppit Over a year ago

yeah I thought about that, but the file runs on a cron and checks every 2 hours for other things and emails... if I do that then on the same day they will get all the emails.

John D · Accepted Answer · 2012-07-10 23:58:54Z

I would suggest normalizing your data set. For example you could have three tables:

ACCOUNT
LOGIN
NOTIFICATION

The structures may be like:

ACCOUNT
-----------------
ID | USER | EMAIL

LOGIN
-----------------
ID | TIMESTAMP

NOTIFICATION
-----------------
ID | TIMESTAMP

Now you could have a view that sits on top of these three tables that only displays an Account ID if the last login timestamp >= today - 4 days. Furthermore you can can get a count from Notification to see how many times you have previously emailed the user or maybe you want to do something like check how much time has passed since the last notification was sent, if it was 5 days send "blabla", but if it is past a certain threshold then send "hello".

So you might email "blablah" when:

LOGIN.TIMESTAMP <= today - 4 days
AND LOGIN.TIMESTAMP > MAX(NOTIFICATION.TIMESTAMP)
AND MAX(NOTIFICATION.TIMESTAMP) - 2NDLARGEST(NOTIFICATION.TIMESTAMP) BETWEEN 5 AND 30

FedeX · Accepted Answer · 2012-07-11 03:22:41Z

0

Marc B answer it's correct but you also need to add another datetime column to user (ie: last_email_sent). Because what your cron php file should do it's check how many days since last login AND if last_email_sent>=4 days OR it's null then you send last_email + 1 and update last_email_sent (to today's date) AND last_email (to last_email+1).

answered Jul 11, 2012 at 3:22

FedeX

4462 silver badges8 bronze badges

Collectives™ on Stack Overflow

mysql/php emailer issue

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related