Sending and Managing Email Accounts in a MySQL Database using Perl
September 30, 2011 3 Comments
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
# https://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. |
|
Tony is the author of Twenty Forty-Four: The League of Patriots
Visit http://2044thebook.com for more information. |
info [at] Scripting
Scripting MySQL 
Impressive, short and very compact in understanding and analyzing ..
most of it i believe wud remain same for php too ..?
if I wanted to update all the emails in my database from @something to @something else. what would be the best way?
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.