Sending and Managing Email Accounts in a MySQL Database using Perl

A friend of mine has a small newsletter that he sends via email once a month. Initially, he was just blind-copying everyone on his list, but as the list grew, it was a pain for him to keep manually editing his BCC list. Also, some people weren’t receiving the newsletter as they had email filters that would block emails that didn’t have their name in the “To” field. And, he needed a way to allow people to opt-out of his newsletter without having to contact him directly. His solution was that he wanted to use a MySQL database to send the email and manage his list, so that he could easily bypass the subscribers that had opted out.

We started by creating a MySQL table with just a few fields – ID, email_address and news_and_events. The “news_and_events” field would be set to “yes” for those that wanted to receive the newsletter, and could be set to “no” if they wanted to opt-out. Here is the database:

CREATE TABLE `email_address_test` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`email_address` varchar(50) NOT NULL,
`news_and_events` varchar(3) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

After he had imported all of the email addresses into the database (including setting the news_and_events column value to “yes”), we were ready to go. We are using a Perl script to connect to the MySQL database and to retrieve all of the email addresses that have a value of “news_and_events = yes”. We can then send each one an email with their email address in the “To” field. We also create a log file to store the results. To manage new subscribers, he modified his web site so that new email addresses would also be stored in the database. An earlier post shows you how to insert information into a MySQL database via Perl.

This script uses a text file to store the database connectivity information. (See this earlier post for instructions on how to use this text file).

This script also includes a “remove me” link (using a Perl script named remove_me.pl) in the email to allow people to opt-out of receiving future emails. I include the remove_me.pl script below the main script.

Here is the main script to send email. There are comments in the script to help you figure out how to modify the script for your use.

#!/usr/bin/perl -w use strict;
use DBI;

my $database;

# create a log file to store the results
open LOG, ">filename.txt" or die "Cannot create output file: $!";

my $id;
my $CurrentLine = 0;

     # connect to database

     $dbh = ConnectToMySql($database);

     $query = "select email_address, id from email_address_test where news_and_events = \'yes\'";

     $sth = $dbh->prepare($query);

     $sth->execute();

while (@data = $sth->fetchrow_array()) {
      $email_address = $data[0];
      $id = $data[1];

     # count the number of emails that we send
     $CurrentLine++;
     
# edit this line to match the location of your sendmail program
$sendmail = "/usr/sbin/sendmail -t";

# you will need to modify this information
$reply_to = "From: Scripting MySQL Blog <info\@return_email.com>\n";
$from = "From: Scripting MySQL Blog <info\@return_email.com>\n";
$subject = "Subject: Test email from MySQL\n";
$to = "To: $email_address\n";

# this is the body of the email - along with the unsubscribe script - remove_me.pl
$content = "<html><body>This is a test email.<p>" .

          "<a href=\"http:\/\/cgi-bin/remove_me.pl?id=$id&email_address=$email_address\">Remove me.<\/a>";
     
     open(SENDMAIL, "|$sendmail") or die "Cannot open $sendmail: $!";
     print SENDMAIL $reply_to;
     print SENDMAIL $from;
     print SENDMAIL $subject;
     print SENDMAIL $to;
# use this line if you want to send a plain text email
#     print SENDMAIL "Content-type: text/plain\n\n";
     print SENDMAIL "Content-type: text/html\n\n";
     print SENDMAIL $content;
     close(SENDMAIL);
     
     # comment these line if you don't want output to your monitor
     print "$CurrentLine Email sent to $email_address - $subject";
     # comment this line if you don't want output to your log file
     print LOG "$CurrentLine Email sent to $email_address - $subject";

     # sleep X seconds after each email is sent
     sleep 1;
     
}

print "\nFinished...$CurrentLine emails were sent.\n";
print LOG "\nFinished...$CurrentLine emails were sent.\n";

close(LOG);

# for more information on this sub-routine and the use of the file accessBLOG, visit
# http://scriptingmysql.wordpress.com/2011/07/27/connecting-to-mysql-with-perl/

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

open(ACCESS_INFO, "<..\/accessBLOG") || die "Can't access login credentials";
     $database = <ACCESS_INFO>;
     $host = <ACCESS_INFO>;
     $userid = <ACCESS_INFO>;
     $passwd = <ACCESS_INFO>;

     # the chomp() function will remove any newline character from the end of a string
     chomp ($database, $host, $userid, $passwd);

my $connectionInfo="dbi:mysql:$database;$host";
close(ACCESS_INFO);

# make connection to database
my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
return $l_dbh;

}

And here is the remove_me.pl script. I don’t provide any error-checking to make sure that the news_and_events value is equal to “yes”, so you will need to edit the script if you want this functionality.

#! /usr/bin/perl

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the MySQL database
use DBI;
use DBD::mysql;
# use CGI for forms
use CGI qw(:standard);

my $database;

$query = new CGI;

$id = $query->param("id");
$email_address = $query->param("email_address");

print header;

# connect to the database and update news_and_events to "no"

     $dbh = ConnectToMySql($database);

     $query = "update email_address_test set news_and_events = 'no' where id = '$id'";
          
     $sth = $dbh->prepare($query);

     $sth->execute();
     
     $sth->finish;

     $dbh->disconnect;

print "Your email address $email_address has been removed.";

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

     open(ACCESS_INFO, "<..\/accessBLOG") || die "Can't access login credentials";
     my $database = <ACCESS_INFO>;
     my $host = <ACCESS_INFO>;
     my $userid = <ACCESS_INFO>;
     my $passwd = <ACCESS_INFO>;

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);
#print "<br>$database $host $userid $passwd <br>";

     my $connectionInfo="dbi:mysql:$database;$host";
     close(ACCESS_INFO);

     chomp($userid);
     chomp($passwd);
     # make connection to database
     my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
     return $l_dbh;

}

exit;

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
About these ads

3 Responses to Sending and Managing Email Accounts in a MySQL Database using Perl

  1. Josh Nigam says:

    Impressive, short and very compact in understanding and analyzing ..
    most of it i believe wud remain same for php too ..?

  2. Sadie says:

    if I wanted to update all the emails in my database from @something to @something else. what would be the best way?

    • Tony Darnell says:

      It depends on the values of the original email addresses and the new domain name that you want to use. Is the email address the same for all entries, or do you have a large number of different email addresses? I would need to see some sample email addresses to give you an answer.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 39 other followers

%d bloggers like this: