CODEWITHSUNDEEP
phpmysqlsql
cppit
cppit

Reputation: 4564

mysql/php emailer issue

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.

Upvotes: 1

Views: 78

Answers (3)

FedeX
FedeX

Reputation: 446

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

Upvotes: 0

John D
John D

Reputation: 2365

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

Upvotes: 1

Marc B
Marc B

Reputation: 360572

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"

Upvotes: 1

Related Questions