4 Steps To Bulk Send Email Newsletters In PHP MySQL

Welcome to a tutorial on how to send bulk emails with PHP and MYSQL. I know, PHP is not exactly made to handle bulk email, and there are strangely very few solutions on the Internet, with some of them being paid scripts. Well, some of those are good, but a few of them got me to shake my head – Especially a “solution” with AJAX long polling.

Just how is this done? Let us walk through an example in this guide – Read on!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

QUICK SLIDES

 

TABLE OF CONTENTS

Download & Notes	Bulk Email	Useful Bits & Links
Tutorial Video	The End

 

 

QUICK NOTES

• Create a database and import the 1-database.sql file.
• Change the database settings in 3b-process.php to your own.
• Run 3b-process.php in the command line.

If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

 

STEP 1) NEWSLETTER DATABASE TABLE

CREATE TABLE `newsletter` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `newsletter`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `name` (`name`);

INSERT INTO `newsletter` (`id`, `name`, `email`) VALUES
(1, 'John Doe', 'john@doe.com'),
(2, 'Jane Doe', 'jane@doe.com'),
(3, 'Apple Doe', 'apple@doe.com'),
(4, 'beck@doe.com', 'beck@doe.com'),
(5, 'Charlie Doe', 'charlie@doe.com');

There is nothing special here, just a simple newsletter table with 3 fields.

• id Subscriber ID, primary key.
• name Subscriber’s full name.
• email Subscriber’s email address.

 

 

STEP 2) PHP NEWSLETTER LIBRARY

<?php
class Newsletter {
  private $pdo = null;
  private $stmt = null;
  private $headers = "";
  private $subject = "";

  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  function __construct () {
    try {
      $this->pdo = new PDO(
        "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET, 
        DB_USER, DB_PASSWORD, [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]
      );
    } catch (Exception $ex) { exit($ex->getMessage()); }
  }

  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    if ($this->stmt!==null) { $this->stmt = null; }
    if ($this->pdo!==null) { $this->pdo = null; }
  }

  // (C) COUNT TOTAL NUMBER OF SUBSCRIBERS
  function count () {
    $sql = "SELECT COUNT(*) `cnt` FROM `newsletter`";
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute();
    $result = $this->stmt->fetchAll();
    return $result[0]["cnt"];
  }

  // (D) GET SUBSCRIBERS
  function get ($start=0, $end=10) {
    $sql = "SELECT * FROM `newsletter` LIMIT $start,$end";
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute();
    return $this->stmt->fetchAll();
  }

  // (E) PRIME EMAIL HEADERS & SUBJECT
  function prime ($headers="", $subject="") {
    $this->headers = $headers;
    $this->subject = $subject;
  }

  // (F) SEND MAIL
  function send ($to, $message) {
    return @mail($to, $this->subject, $message, $this->headers);
  }
}

This looks massive, but keep calm and look carefully. Not going to explain line-by-line, but here’s a quick summary.

• (A & B) When a new Newsletter() object is created, the constructor connects to the database. The destructor closes it.
• (C To F) There are only 4 newsletter-related functions.
  • count() Counts the total number of subscribers.
  • get() Get subscribers. Not a good idea to fetch thousands of entries at once, so this is limited.
  • prime() Primes the email headers and subject.
  • send() Send out the email itself.

 

 

STEP 3) BULK SEND EMAIL NEWSLETTERS

Of course, the newsletter library will not do the magic by itself. So we will work on the actual “newsletter processing script” in this step.

 

EMAIL TEMPLATE

<html><body>
Dear [NAME],<br>
<p>There will be a super sale going on next week!</p>
<p>All items will be going at a 20% discount, so grab it while stocks last!</p>
</body></html>

Before we go into the actual processing script, this is the demo email template that will be sent out. Take note that [NAME] will be replaced with the subscriber’s name later, and please feel free to change this template to whatever you want.

 

PHP MAIL PROCESSING SCRIPT

<?php
// (A) INIT + RUN SETTINGS
set_time_limit(0); // No timeout
$each = 10; // Get 10 subscribers each run
$pause = 1; // 1 sec pause between each email send

// (B) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (C) LOAD LIBRARY + EMAIL TEMPLATE FROM FILE
require "2-newsletter.php";
$news = new Newsletter();
$template = file_get_contents("3a-template.html");

// (D) EMAIL SUBJECT + HEADER
$subject = "[STORE] Crazy sales";
$headers = implode("\r\n", [
  "From: abc@xyz.com",
  "Reply-To: abc@xyz.com",
  "MIME-Version: 1.0",
  "Content-Type: text/html; charset=utf-8"
]);
$news->prime($headers, $subject);
unset($subject); unset($headers);

// (E) SEND THE EMAIL - BATCH BY BATCH
$all = $news->count();
for ($i=0; $i<$all; $i+=$each) {
  $subscribers = $news->get($i,$each);
  foreach ($subscribers as $sub) {
    $msg = str_replace("[NAME]", $sub["name"], $template);
    $news->send($sub["email"], $msg);
    // If you want to keep a pass/fail send log
    // $pass = $news->send($sub["email"], $msg);
    // if ($pass) { SAVE TO LOG FILE OR DATABASE }
  }
  sleep($pause);
}

The process script itself should be pretty easy to understand, but here is a summary:

• (A & B) Just some initialization and settings. Please remember to change the database settings to your own.
• (C & D) Loads the library we created earlier, and primes the subject/header of the newsletters.
• (E) The emails will be sent out in batches, not “all-at-once” to prevent flooding.
  • That is, we send out $each emails at a time.
  • Then followed by a $pause seconds wait.
  • Feel free to tweak the $each and $pause settings to your own liking… To an amount that does not break your server.

 

 

STEP 4) RUN SCRIPT IN COMMAND LINE

The last step is to run the “send newsletter” script from the command line.

 

4A) COMMAND-LINE RUN

# FOR WINDOWS
D:\http\test>php 3b-process.php

# LINUX OR MAC
php -f /var/www/test/3b-process.php

Manually start and send anytime you want.

 

4B) SCHEDULED RUN

Alternatively, we can also schedule the script to run at a certain time.

• Windows users – Just use the Task Scheduler.
• Mac and Linux users – Use Cron.

If you are not sure how to do it, I will leave links in the extras section below.

 

4C) BACKGROUND RUN

How to Run PHP Scripts In The Background – Simple Examples

Lastly, use PHP to run a PHP script in the background on demand. Dumb trolls can scream “this is so stupid and complicated” all they want. I prefer “one click and go drink tea”. Not “stare at a long-poll AJAX screen, close it accidentally and start all over again”.

 

 

SETTING MYSQL TIMEOUT

When you are extracting a huge amount of data from the database, you will sometimes get a timeout. It might be wise to allocate a little more time to run the queries, and here is how to set the MYSQL query timeout:

Server-side SELECT statement timeouts

 

 

LOG YOUR SEND HISTORY!

Learn from my mistakes. So what happens if the script runs halfway and dies? Re-run everything again and double send the newsletter? Nope. I will highly recommend that you keep a log of the newsletters that you have sent to the database. For example:

CREATE TABLE `news_sent` (
  `newsletter_id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `news_sent`
  ADD PRIMARY KEY (`newsletter_id`,`email`),
  ADD KEY `timestamp` (`timestamp`);

• newsletter_id is the unique ID of your campaign.
• email… the email address of the user.
• timestamp is the time at which the email has been sent out to the user.

With this, we can modify the send() library function to record after every successful email send:

if (mail(TO, SUBJECT, MESSAGE, HEADERS)) {
  // SAVE SEND HISTORY
  $sql = "INSERT INTO `news_sent` (`newsletter_id`, `email`) VALUES (ID, EMAIL)";
  // ...
}

Finally, do a SELECT * FROM `newsletter` WHERE `email` NOT IN (SELECT `email` FROM `news_sent` WHERE `newsletter_id`=ID) instead, and that should exclude all the subscribers who already got the email.

 

LINKS & REFERENCES

• Email not sending out in PHP? Here’s how to fix it – Code Boxx
• Mail Function – PHP
• How to create an automated task using Task Scheduler on Windows 10 – Windows Central
• How to use cron in Linux – opensource.com

 

TUTORIAL VIDEO