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!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

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

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

TUTORIAL VIDEO

 

STEP 1) NEWSLETTER DATABASE TABLE

CREATE TABLE `newsletter` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

ALTER TABLE `newsletter`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
 
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', '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 {
  // (A) PROPERTIES & SETTINGS
  private $pdo = null; // pdo object
  private $stmt = null; // sql statement
  private $from = "promo@site.com"; // email from
  private $each = 10; // get 10 subscribers each send cycle
  private $pause = 1; // 1 sec pause between each cycle

  // (B) CONSTRUCTOR - CONNECT TO DATABASE
  function __construct () {
    $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
    ]);
  }

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

  // (D) COUNT TOTAL NUMBER OF SUBSCRIBERS
  function count () {
    $this->stmt = $this->pdo->prepare("SELECT COUNT(*) `cnt` FROM `newsletter`");
    $this->stmt->execute();
    return $this->stmt->fetchColumn();
  }

  // (E) GET SUBSCRIBERS
  function get ($start=0, $end=10) {
    $this->stmt = $this->pdo->prepare("SELECT * FROM `newsletter` LIMIT $x, $y");
    $this->stmt->execute();
    return $this->stmt->fetchAll();
  }
 
  // (F) SEND NEWSLETTER
  function send ($subject, $template, $replace=null) {
    // (F1) EMAIL HEADERS
    $header = implode("\r\n", [
      "From: " . $this->from,
      "MIME-Version: 1.0",
      "Content-Type: text/html; charset=utf-8",
    ]);
 
    // (F2) LOAD TEMPLATE
    if (!file_exists($template)) {
      $this->error = "$template not found";
      return false;
    }
    $template = file_get_contents($template);
 
    // (F3) SEND BATCH BY BATCH
    for ($i=0; $i<$this->count(); $i+=$this->each) {
      $subs = $this->get($i, $this->each);
      foreach ($subs as $s) {
        // (F3-1) EMAIL TEMPLATE REPLACE
        $msg = $template;
        if ($replace != null) { foreach ($replace as $from=>$to) {
          $msg = str_replace($from, $s[$to], $msg);
        }}
 
        // (F3-2) SEND - SAVE THE SEND STATUS HERE IF YOU WANT
        if (@mail($s["email"], $subject, $msg, $header)) {
          // OK
        } else {
          // NOT OK
        }
      }
 
      // (F3-3) NEXT CYCLE
      sleep($this->pause);
    }
  }
}
 
// (G) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");
 
// (H) NEWSLETTER OBJECT
$_NEWS = new Newsletter();

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

• (B, C, H) When $_NEWS = new Newsletter() is created, the constructor automatically connects to the database. The destructor closes the connection.
• (D, E, F) There are only 3 newsletter-related functions.
  • count() Counts the total number of subscribers.
  • get() Get subscribers.
  • send() Send out a newsletter. Not a good idea to dump thousands of entries all at once, so this one is done by the batches instead.
• (G) Self-explanatory. Change the settings to your own.

 

 

STEP 3) BULK SEND EMAIL NEWSLETTERS

3A) 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>

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.

 

3B) PHP MAIL PROCESSING SCRIPT

<?php
// (A) LOAD LIBRARY
require "2-lib-newsletter.php";
 
// (B) SEND!
$_NEWS->send(
  "[STORE] Crazy sales", // subject
  "3a-template.html", // email template
  ["[NAME]" => "name"] // replace "[NAME]" with database name column
);

Nothing much, load the library and use it to send out the newsletter – Just specify the subject, template to use, and which variables to replace.

 

 

STEP 4) RUN SCRIPT IN COMMAND LINE

4A) COMMAND-LINE RUN

# FOR WINDOWS
php 3b-process.php

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

Lastly, simply run the “processing script” in the command line and let it roll.

 

4B) SCHEDULED RUN

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

• Windows users – 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

Another alternative is to use PHP to run another PHP script in the background on demand. Dumb trolls can scream “this is so stupid and complicated” all they want, but I prefer to “click and go drink tea”… Not “click and stare at a long-poll AJAX screen”.

 

 

SETTING MYSQL TIMEOUT

When you are extracting a huge amount of data from the database, you will sometimes get a timeout. It will 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
• Send Email With HTML Template In PHP – 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