MassMailer template v2 was updated. This is a free upgrade for existing MassMailer template v2 owners. More info.
MassMailer template can be used for more than just sending bulk emails. Lets consider the case where you manage a table with usernames and passwords for your internal company application. For increased security users should change passwords every 60 days. As an administrator you need to perform two standard tasks:
- Set reminders to users when their password is about to expire
- Mark their account as inactive when password expires
We’ll show how this can be done with the help of MassMailer template. In this example we’ll be using MySQL and here is how our table with logins and passwords looks. Only users where ‘active’ field equals 1 are able to logon.
1. Send a reminder two days before password expires
This is fairly straightforward. Here is the SQL query we can use:
select * from login where DATEDIFF(expires, now())=2
Assuming that today is January 29th, 2016 this query returns one record (dave.p). We can setup this task to run once a day and Dave.P will receive a nice “do not forget to change your password” reminder.
Please note that this SQL query syntax is MySQL specific. Refer to Date/time handling in web applications article for other databases syntax.
2. Make login expire
This one is more interesting. This task is going to also run once a day and change ‘active’ column for those accounts that expire today to 0.
SQL Query is very similar:
select * from login where DATEDIFF(expires, now())=0
Email body will say something like this:
Dear %username% Your account has expired. Call administrator at 1-800-XXX-XXXX in order to restore your access.
This is what we going to put into ‘Execute SQL query after email sent’ field
update login set active=0 where id = %main.id%
This is it. Now we have two tasks that take care of sending reminders and marking accounts as expired. In real life you may want to send more reminders or do something more sophisticated than make your users call system admin when their accounts expires.
Happy coding!