Using MySQL Triggers to Send Activation Emails

Have an eFront custom job? Here is the place to post it.
Are you a developer looking for an eFront-based job? Please check this forum.

Using MySQL Triggers to Send Activation Emails

Postby bradartigue » Fri Aug 09, 2013 6:11 pm

Hi, I'm new here...

I created a method for sending activiation emails to users. This seems to be a common topic and I had the same issue, our process must have an activation email sent once the admin completes the verification of the account. This requires no modification of efront code, which is nice, and relies on a simple mysql trigger and a new table to do the work.

The process is as follows, steps 1-3 are normal efront behavior, 4-6 are performed by this customization:

1) a user self-registers
2) the registration is held in queue as inactive
3) the administrator marks the user account active
4) a mysql trigger places the new, active account information into a table called users_activations
5) a php script, activated by a cron job, reads users_activations and dispatches emails to the newly activated accounts
6) the script deletes all records in users_activations

You need to know how to use mysql, PHP, and cron, and have your server configured so that PHP can send mail. My server is a fedora 19 box with postfix as the mailer, however, i've tested it with regular sendmail as well.


Step 1. Create a database table to store the new user information:

Code: Select all
Create table users_activated(id mediumint(8) unsigned, email varchar(150), login varchar(100), active tinyint(1));


Step 2. Create a trigger in your mysql database as below. This will insert a new row into the users_activated table whenever an account is activated by the administrator.

Code: Select all
delimiter $$
CREATE TRIGGER trigger_active_change BEFORE UPDATE ON users
    FOR EACH ROW
    BEGIN
         IF NEW.active = 1 THEN
             insert ignore into users_activated(id,login,email,active) values(new.id,new.login,new.email,new.active);
         END IF;
    END;$$
delimiter ;


Step 3. Write a PHP script to parse the users_activated table and send an email to each. Mine is saved as /usr/local/efront/www/sendactemail.php:

Code: Select all
<?php
$username = "(user name)";
$password = "(password)";
$hostname = "(hostname or ip address)";
$dbname="(your database name)";

//connection to the database
   $dbhandle = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL");
   echo "Connected to MySQL...";
   $selected=mysql_select_db($dbname,$dbhandle) or die($dbname. " is not online");
   $result = mysql_query("SELECT id,email,login,active from users_activated where active=1");

   echo "Parsing rows and sending email...";

   while ($row = mysql_fetch_array($result)) {
           $to=$row{'email'};
          echo($to."...");
         $subject="Elearning Account Activated";
          $body='Your account on the efront system has been activated.  You may now log into http://your_URL with the credentials you supplied.';
           if (mail($to, $subject, $body, 'From: your@emailfortheefrontsystem.com'))
           {
                   echo("sent....");
           }
           else
           {
                   echo("failed...");
           }
}

//delete all rows from trigger table
$result=mysql_query("DELETE FROM users_activated");
if($result=0) echo "Database delete failure";
mysql_close();
echo "Complete!";


Step 4: Add a cron job to run this script how ever often you wish, for example, every 5 minutes:

Code: Select all
sudo crontab -u apache -e
*/5 * * * * /usr/bin/php /usr/local/efront/www/sendactemail.php


Step 5: Test

Go into your efront system and deactivate a user; then active them. If you have a mysql client open you can:

Code: Select all
select * from users_activated


If your trigger is doing its job, you'll see the account. Then, on your server,

Code: Select all
/usr/bin/php /usr/local/efront/www/sendactemail.php

And it will run, parse the table, send the email, and delete the entry from the users_activated table.


Next steps

I think it fairly obvious you can add more columns to the users_activated table and thereby add more content to the email message that you send. You can also customize the body to include HTML or more or less text.

I hope this helps those of you looking to do this with your system.
bradartigue
 
Posts: 2
Joined: Fri Aug 09, 2013 5:53 pm

Re: Using MySQL Triggers to Send Activation Emails

Postby elpapath » Tue Aug 27, 2013 2:56 pm

Hello,

Thank you for sharing this information with us. :)

Eleftheria
elpapath
 
Posts: 667
Joined: Wed Jun 18, 2008 9:25 am

Re: Using MySQL Triggers to Send Activation Emails

Postby bradartigue » Wed Sep 04, 2013 4:04 pm

After posting and testing I realized that efront is using the "Active" flag for much more than just setting an account active, and it appears to set this flag when you switch users or even when a lesson is completed. If you're experiencing the same thing then you can create the trigger on other tables, for example, we use groups. We put the trigger on the users_to_groups table and simplified the users_activated table to only have the login id...

the table USERS_ACTIVATED now looks like this:

Code: Select all
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| users_LOGIN | varchar(100) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+


The trigger explained previously was deleted and replaced with trigger_users_to_groups, which activates on the insert of a new row into users_to_groups:

Code: Select all
 begin insert into users_activated(users_LOGIN) values(NEW.users_LOGIN);


And finally the code to read the users_activated table was altered as in the following PHP:

Code: Select all
<?php
//put in your values
$username = "";
$password = "";
$hostname = "";


//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
  or die("Unable to connect to MySQL");
echo "Connected to MySQL...";
$selected=mysql_select_db("your_db_name",$dbhandle) or die("database is not online");
$result = mysql_query("SELECT distinct a.users_LOGIN, b.email from users_activated a inner join users b on a.users_LOGIN=b.login;");

echo "Parsing rows and sending email...";

while ($row = mysql_fetch_array($result)) {
        $to=$row{'email'};
        $login=$row{'users_LOGIN'};
        echo($to.'('.$login.')...');
        $subject="Account Activated";
        $body='some message here';
        if (mail($to, $subject, $body, 'From: email@email.com'))
        {
                echo("sent....");
        }
        else
        {
                echo("failed...");
        }
}
//delete all rows from trigger table
$result=mysql_query("DELETE FROM users_activated");
if($result=0) echo "Database delete failure";


mysql_close();
echo "Complete!";
bradartigue
 
Posts: 2
Joined: Fri Aug 09, 2013 5:53 pm


Return to Customizations

Who is online

Users browsing this forum: No registered users and 1 guest

cron