Simple Cross Language Bulk Mailer Part 1

If you've spent a decent amount of time in the Web development field, chances are you've had to send out a 'mass mailing' of some sort at one time or another. When done manually, this can be an extremelly tedious task. Creating 'groups' of email addresses within your email client is one solution, but what if a co-worker wanted to send an email out to the group of people as well? Obviously, this is a process which is screaming for automation. In the first section of this three-part article, we'll discuss the foundation for a simple mass mailer.

Spelling out our goals.

Now, what we're creating here is going to be an *extremely* simple mass emailer that will allow us to send a single email to many many people through a simple web form. This isn't to be confused with a full fledged mailing list manager(MLM) such as Mailman or Majordomo which would facilitate replies, digests, archives, etc. We're here to do one(albeit simple) thing and do it well. However, we will build our application so that if the need arises for more features in the future, they won't be difficult to integrate into what we'll have already built. The first part of this article will deal with setting up the database structure, and talking about some of the high level goals we'll be trying to achieve. The continuation of this article will give examples in Cold Fusion and PHP. Perhaps someone would like to also write ASP or JSP versions of the code using the strucutre we'll be explaining today as a jumping point.

The database architecture.

First off, let's get the database structure defined. We have three tables in our application; one for messages, one to keep track of users, and one to define lists. Here's the SQL and a basic description of each:

## Created in MySQL, adjust to taste.
CREATE TABLE lists (
list_id int(3) DEFAULT '1' NOT NULL,
list_email varchar(50) NOT NULL,
list_admin_id int(11) DEFAULT '0' NOT NULL,
list_desc text NOT NULL,
list_name varchar(50) NOT NULL,
list_passwd varchar(50) NOT NULL,
list_footer varchar(250) NOT NULL,
UNIQUE list_id (list_id)
);

So, our lists table has 6 values:

• list_id each list has unique numeric value, its our primary key(PK).
• list_email holds the value of what our list will show in the From: header of the email.
• list_admin_id is just a placeholder for future use and won't be used here.
• list_desc is a short description of our list.
• list_name is the name of our list, and what we'll use to authenticate administrators.
• list_passwd is the password used to gain access to the functions of our list.
• list_footer is a message that will get appended to each message sent out with information about our list.

# Table structure for table 'members'

CREATE TABLE members (
member_id int(1) DEFAULT '1' NOT NULL,
member_fname varchar(50),
member_lname varchar(50),
member_email varchar(50) NOT NULL,
member_level int(11) DEFAULT '0' NOT NULL,
member_subscribed tinyint(1) DEFAULT '0' NOT NULL,
member_created datetime NOT NULL,
member_subscribedto int(11) DEFAULT '0' NOT NULL,
UNIQUE member_id (member_id)
);

A description of the members table:

• member_id assign each member a unique id, our PK.
• member_fname first name of the member.
• member_lname last name of the member.
• member_email email address of the member.
• member_level placeholder for future features and won't be used here.
• member_subscribed 0/1 value to tell us if the member is subscribed or unsubscribed.
• member_created just a date/time stamp to see how long they've been subscribed.
• member_subscribedto id of the list they're subscribed to.

# Table structure for table 'messages'

CREATE TABLE messages (
msg_id int(1) DEFAULT '1' NOT NULL,
msg_from_id int(11) DEFAULT '0' NOT NULL,
msg_body text NOT NULL,
msg_date datetime NOT NULL,
msg_list_id int(11) DEFAULT '0' NOT NULL,
msg_subject text,
UNIQUE msg_id (msg_id)
);

A description of the messages table:

• msg_id a unique value for each message that is sent, our PK.
• msg_from_id placeholder for future features that won't be used here.
• msg_body the body of the email thats being sent out.
• msg_date a date/time stamp recording when the message was sent.
• msg_list_id a number that corresponds to a list_id in our lists table to track which message belongs to which list.
• msg_subject the subject of the email being sent.

As you can see, pretty much everything ties into together. We're planning on making this somewhat 'scaleable' in that we can have more than one list, and that each list we define has a seperate set of subscribers and messages associated with that particular list. Likewise, every member and message is associated with a list_id. Other than that, it's pretty self-explanatory.

Now that we've got the database foundation nailed down we'll continue with the Cold Fusion end of our application will look like in the next installation of this article. In following installments, we'll show how to provide the same functionality in PHP that uses the same foundation we've layed today. Hopefully, someone will also follow up with ASP and/or JSP versions of the code too. Stay tuned!