Johan Broddfelt
/* Comments on code */

Build a mailinglist

Usually when I read about building mailing lists, it is some marketing guru writing all about getting people to sign up to your mailing list. This is not it, today we are going to writ the code that will maintain our mailing list. In my last post I created a Mail class that enables us to send mails. And we are going to use that class for this project. Of course there are a ton of good services out there, the most used probably being MailChimp. But we do not learn how to build applications by using MailChimp do we?

We also have a list of users in our User class. So let's use that one to keep track of our mailing list users as well. What we do not have is an actual defined mailing list and a way to register users to that list. Actually I would argue that each post should be considered a mailing list, because as a user you might want to receive mails if someone is posting a reply to your comment. Then I need a master mailing list that you could register to if you want my newsletters or a mail every time I post something new on my site. But later I'm also going to add different topics and each topic might also be considered a mailing list you might want to register to. In order to manage this I have a simple trick I use, you have seen it in the Comment class. It is an item and a type column in the database table. So I will basically not do a the mailing list table and only do a table that refers the user to a hypothetical mailing list. Where type indicates what you are following: 0 could be the main mailing list, 1 would then be posts and 2 indicate a topic. Here is our new table.

CREATE TABLE IF NOT EXISTS `subscription` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `type` int(11) NOT NULL COMMENT '0: Mailing list, 1: Posts, 2: Topics',
  `item_id` int(11) NOT NULL,
  `created` date NOT NULL,
  `closed` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1 ;

Not that we do not actually need to use the id column for this table. And I should probably have skipped it. But it makes it easier to reuse the current code in our Db class. I will show you a relation table class at some later point. Another thing to notice here is that I use Date for created and closed. DateTime would probably be more logical, but I don't really need that specific information and I need a sample of a date column in order to write the datepicker in an upcoming post. You could also argue that I should simply delete the post if the user unregisteres from a newsletter. But in this case I want the history, because it could be useful to see if someone is a returning customer or if someone else is continually adding him to my list without his consent. In that case I could block that mail completely. Any way it is always good to have some history about your users.

Now we would like to create a registration for for our mailing list. And in this case we need to do more than just create an edit form. Because the visitor should only have to type his or her mail address and then the system should take care of the rest.

// views/subscription/register.php

<?php
$valid = false;
$active = false;
$mail = strtolower(Http::filter_input(INPUT_POST, 'mailing_list_mail'));
if ($mail != '') {
    // Validate the mail address
    if (filter_var($mail, FILTER_VALIDATE_EMAIL)) {
        $valid = true;
    }
    if ($valid) {
        // Check if we have a user registered with that specific mail
        $uArr = $user->fetchArray('WHERE LOWER(mail) LIKE '' . $mail . ''');
        $u = $uArr[0];

        // If not then register the user
        if ((int)$u->id == 0) {
            $u = new User();
            $u->mail = $mail;
            $mailArr = explode('@', $mail);
            $u->username = $mailArr[0];
            $u->created = date('Y-m-d H:i:s');
            $u->status = 0; // Indictes that this is not an active user of our system.
            $u->update();
        }

        // We do not want to add a user multiple times, so we need to check if the user already is registered
        $sArr = $obj->fetchArray('WHERE user_id=' . $u->id . ' AND closed LIKE '0000-00-00'');
        if ($sArr[0]->id > 0) {
            $active = true;
        }
    }
    if ($valid and !$active) {
        // Add the user to our mailing list
        $obj->userId = $u->id;
        $obj->type = 0; // Mailing list
        $obj->itemId = 1; // We have only one mail mailing list, that is item 1, later we might add a newsletter for paying customers perhaps
        $obj->created = date('Y-m-d');
        $obj->update();
        echo '<div class="information">Thank you for registering to my mailing list.</div>';
    } else if (!$valid) {
        echo '<div class="error">You entered an invalid mail address!</div>';
    } else if ($active) {
        echo '<div class="warning">You are already subscribing to this list.</div>';
    }
}
if (!$valid and !$active) {
    // If the user did register we do not want to show this form again.
    ?>
    <div>
        <h2>Newsletter</h2>
        <p>If you want updates about my posts then register to my newsletter.</p>
        <form method="POST" action="">
            <input type="text" name="mailing_list_mail" placeholder="you@domail.com">
            <input type="submit" name="register" value="Register">
        </form>
    </div>
    <?php
}

Ok, so now you have your followers registered. What if they want to unregister? We need to create a view for that as well. But we do not just want anyone to be able to enter a mail address to unregister. We want to make sure that it is someone that has received a mail from us that is doing the unregistering. So we need a unregister link that we can easily provide within each sent mail. Containing the users mail and the current mailing list sending the specific mail. And we do not want to use the users id, because that is also easy to guess or even figure out. So what want to do is use mail in combination with subscription id. We could also create a unique key for each user but that is too much work right now. In order to make this look nice in a url we just run an md5() on the values with some salt before creating the url.

// classes/Subscription.php

<?php
class Subscription extends Db {
    public $table = 'subscription';
    
    function getSubscriptionKey() {
        return md5($this->id.'some_salt'.$this->userId);
    }

    function getSubscribers($type, $itemId) {
        $retArr = array();
        $s = new Subscription();
        $subscribers = $s->fetchArray(' WHERE type=' . $type . ' AND item_id=' . $itemId . ' AND closed='0000-00-00'');

        foreach ($subscribers as $sub) {
            $sub->user = new User((int)$sub->userId);
            $retArr[] = $sub;
        }
        return $retArr;
    }
}

I also added a getSubscribers function, because I know that I will need that later. Now that we have our key we can generate a url that looks like this: http://mysite.com/index.php? module=subscription&view=unregister&id=1&key=64455180faa7a15cfb4d1e1dc64056a7. And here is the code that manages the unregistering of subscribers.

// views/subscription/unregister.php

<?php
$key = Http::filter_input(INPUT_GET, 'key');
if ($key == $obj->getSubscriptionKey()) {
    $obj->closed = date('Y-m-d');
    $obj->update();
    echo '<div class="information">You have successfully been removed from this mailing list.</div>';
} else {
    echo '<div class="warning">You could not be found in our mailing list, please contact me at me@mail.com if you are having problems.</div>';
}

We also need to add these files to our public pages array in index.php


    $publicPages = array(
        'post_items',
        'post_item',
        'user_login',
        'page_*',
        'subscription_register',
        'subscription_unregister'
    );

Is that it? Well no. We also want to send some mails right. Here we run in to a completely different set of issues. If I were to send all the mails directly when I create a post, then it might take a long time to save and the server might even timeout if I have a long list of subscribers. So then I might choose to only save the mails to my mail table with Mail::registerMail(). But that might also be a lot of work, at least for the mail mailing list. It would probably work fine when sending mails on post comments. But we want a separate script we can run that generates the posts in our mail table. And then we want another script running through all unsent mails and sending them. Here is another issue. It might be that this job takes a lot of resources on the server so that you want to run that at night or perhaps only send a few mails every 5 minutes. Currently I'm hosting this site at a web hotel that "only" allows 250 mails per 5 minutes. So I need to make sure that I stay below that while using this host. First of all I want to add a column to my post table, in order to keep track if I have sent it to my subscribers or not.

ALTER TABLE `post` ADD `sent` DATETIME NOT NULL;

I want to add a file that generates post mails in my mail table. In order to keep track of which post has been sent I need to add a sent column in the post table. Then I create a script that loops all my mailing list users and create a mail in the mail table.

// cron/mailinglist.php
<?php
// Because we are storing all our cron jobs in a sub folder we need ../ to find the global.php
require_once('../global.php');

// Get list of unsent posts, limit to 1 because we do not want to spam our subscribers by mistake
$p = new Post();
$posts = $p->fetchArray(' WHERE sent='0000-00-00 00:00:00' ORDER BY id LIMIT 1');

// Get a list ot subscribers
$subscribers = Subscription::getSubscribers(0, 1); // 0 = Mailinglist, 1 = item 1 because that is the one list I have

// Create a mail object
$m = new Mail();

// Loop unsent posts. Should hopefully only be one 
foreach ($posts as $post) {
    foreach ($subscribers as $sub) {
        #$u = new User((int)$sub->userId);
        #echo $u->mail.'<br>';

        $m->to = $sub->user->mail;
        $m->subject = $post->title . ' - JohanBroddfelt.se';
        $m->message = '' . $post->title . PHP_EOL . PHP_EOL
                    . PHP_EOL . $post->summary 
                    . PHP_EOL . PHP_EOL . 'Read the entire post at: http://www.johanbroddfelt.se/' . $post->linkTitle() . '-' . $post->id 
                    . PHP_EOL . PHP_EOL . 'Unsubscribe to post newsletter by clicking the link below:' 
                    . PHP_EOL . 'http://www.johanbroddfelt.se/index.php?module=subscription&view=unregister&id=' . $sub->id . '&key=' . $sub->getSubscriptionKey();
        
        //echo $m->message . '<hr>';
        // Right now we only want to register the mails in the database so that we can send them later
        $m->registerMail();
    }
    // Mark the post as sent so that we do not resend mails about this post again
    $post->sent = date('Y-m-d H:i:s');
    $post->update();
    echo 'Added ' . count($subscribers) . ' subscribers to mailinglist for (' . $post->id . ') ' . $post->title . PHP_EOL;
}

And last but not least the script that actually is sending my mails.

// cron/exec_mail.php
<?php
// This script will run every 5 minutes sending all mails that has not been registered as sent in our mail table

// Because we are storing all our cron jobs in a sub folder we need ../ to find the global.php
require_once('../global.php');

// Get list of unsent mails. My host provider has a limitation od 250 mails every 5 minutes. So let's stay below
$m = new Mail();
$mails = $m->fetchArray(' WHERE sent='0000-00-00 00:00:00' ORDER BY id LIMIT 100');

// Create a mail object
$m = new Mail();

// Loop unsent mails and send them
foreach ($mails as $mail) {
    $mail->sendMail();
}

This host does not have a crontab functionality but since I have another server I will call these scripts from there with an appropriate interval. Otherwise you could rely on your users to trigger events by executing some ajax calls in the background or something like that. Or you my decide to run the script manually your self when you actually want to send larger amounts of mail out so that you have more control.

Now there is only one thing left, and that is to add the "Call To Action" on our start page by adding the following code: <a href="register-subscription-url" id="cta_newsletter">Newsletter sign up</a> and then style the #cta_newsletter to your liking in the css. And hope for all your visitors to register.

The last thing we want to do in order to complete this post is to add our cron jobs to the cron file. Here is how I do that in my case.

// crontab on my server
# johanbroddfelt.se jobs
*/5 * * * * nice -n 13 wget http://johanbroddfelt.se/cron/exec_mail.php > jb_exec_mail.log >/dev/null 2>&1; rm exec_mail.php* >/dev/null 2>&1
42 * * * * nice -n 13 wget http://johanbroddfelt.se/cron/mailinglist.php > jb_mailinglist.log >/dev/null 2>&1; rm mailinglist.php* >/dev/null 2>&1

If I have the cronjob on the same server as the site is hosted I prefer to run the script via the PHP command instead. Because then I'm not a victim to server timeout, DNS issues and network problems that can arise when routing through the Internet. >/dev/null 2>&1 looks a bit strange, but that actually prevents the cron from sending a mail to the server admin mail account every time the cronjob is executed.

- Framework, PHP, Mailinglist

Follow using RSS

<< Send me an email SEO friendly urls >>

Comment

Name
Mail (Not public)
Send mail uppdates on new comments
0 comment