Bulk Send Email Newsletters with 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.

A more reliable way to bulk send email newsletters using PHP and MYSQL is:

Create a database table to store the list of subscribers.
Create a PHP library to manage and process the newsletters.
Lastly, create and run a command line PHP script that will get the list of subscribers and send out newsletters batch-by-batch.

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.

REAL QUICK TUTORIAL

How To Bulk Send Emails In PHP

Download & Notes	Newsletter Database	Newsletter Library
Send Email	Command Line Run	Useful Bits & Links
What Next?

DOWNLOAD & NOTES

First, here is the download link to the source code as promised.

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.

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, please feel free to comment below. I try to answer 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.

NEWSLETTER DATABASE

For the purpose of demonstration, we shall use a simple newsletter table and some dummy subscribers.

NEWSLETTER TABLE

1-database.sql

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'),
(6, 'Charles Doe', 'charles@doe.com'),
(7, 'Dion Doe', 'dion@doe.com'),
(8, 'Dee Doe', 'dee@doe.com'),
(9, 'Emily Doe', 'emily@doe.com'),
(10, 'Ethan Doe', 'ethan@doe.com'),
(11, 'Frank Doe', 'frank@doe.com'),
(12, 'Gina Doe', 'gina@doe.com'),
(13, 'Hela Doe', 'hela@doe.com'),
(14, 'Hubert Doe', 'hubert@doe.com'),
(15, 'Ivy Doe', 'ivy@doe.com'),
(16, 'Ingrid Doe', 'ingrid@doe.com'),
(17, 'James Doe', 'james@doe.com'),
(18, 'Jace Doe', 'jace@doe.com'),
(19, 'Kate Doe', 'kate@doe.com'),
(20, 'Luke Doe', 'luke@doe.com');

Field	Description
id	Primary key, the subscriber ID.
name	The full name of the subscriber.
email	Email address of the subscriber, set to unique to prevent double subscriptions.

PHP NEWSLETTER LIBRARY

Next, let us work on the PHP newsletter library to manage the subscribers and newsletters.

NEWSLETTER LIBRARY SCRIPT

2-newsletter.php

<?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) { die($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);
  }
}

Function	Description
__construct()	The constructor, connects to the database when a newsletter object is created.
__destruct()	The destructor, closes the database connection when the newsletter object is destroyed.
count()	Gets the total number of subscribers in the database.
get()	Get subscribers. It is not a good idea to just “select all” when you have thousands of subscribers, thus the use of limit.
prime()	Primes the newsletter email headers and subject.
send()	Sends out the newsletter email.

SEND NEWSLETTER TO SUBSCRIBERS

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

EMAIL TEMPLATE

Before we go into the actual processing script, this is the demo email template that will be sent out:

3a-template.html

<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>

Please 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

3b-process.php

<?php
// (A) INIT + RUN SETTINGS
set_time_limit(0); // No timeout
$each = 5; // Get 5 subscribers each round
$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 of it:

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.

RUN FROM THE COMMAND LINE

The last step is to run the “send newsletter” script from the command line. Yes, we don’t want to stare at a “still loading” screen, and accidently break the process by closing the browser.

COMMAND LINE RUN

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

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

SCHEDULED RUN

Alternatively, we can 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.

USEFUL BITS & LINKS

That is a fully functioning bulk email sender script, but it is only the start; It is only a skeleton and a lot of work needs to be done on top of it. Here are some tips and extras that may be useful.

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 in 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.

YOUTUBE TUTORIAL

LINKS & REFERENCES

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

THE END

Thank you for reading, and we have come to the end of this guide. I hope it has helped you with your project, and if there is anything that you wish to add to this guide, please feel free to comment below. Good luck and happy coding!

8 thoughts on “Bulk Send Email Newsletters with PHP MySQL – Free Code Download”

AUSTEIN
November 19, 2020 at 8:34 am

it can be the
function count() <<<<<<<
from php 7 on does not work this way anymore
i have no other idea why it is not possible
1. W.S. Toh
  November 19, 2020 at 8:46 am
  
  Mate, the error clearly states that you have a duplicate function count() somewhere. Sorry, but I cannot offer free project consultation and troubleshooting beyond this. Good luck with your project!
Austein
November 18, 2020 at 1:58 pm

could you please help me
I get the message
>>> Fatal error: Cannot redeclare count() in C:\xampp\htdocs\Sign up\2-newsletter.php on line 32
but I cannot find the error
1. W.S. Toh
  November 18, 2020 at 3:17 pm
  
  Fatal error: Cannot redeclare count() in C:\xampp\htdocs\Sign up\2-newsletter.php on line 32
  PHP is already telling you where the error is? Anyway, made a small change to (D) GET SUBSCRIBERS – $sql = "SELECT * FROM `newsletter` LIMIT $start,$end"; Passing the LIMIT through execute([$start, $end]) somehow causes it to “complain”.
MIchael Perdun
January 30, 2020 at 10:43 pm

Would you please expand on the send history. Should I give each email a newletter_id or ad it to the code prior to sending? Again Great Code share!
Long
January 21, 2020 at 4:58 pm

Can you help me?
I have a problem.
“[21-Jan-2020 11:55:03 America/New_York] PHP Notice: Undefined property: Newsletter::$CB in /home/forcjuny/public_html/newsletter.php on line 20
[21-Jan-2020 11:55:03 America/New_York] PHP Fatal error: Call to a member function verbose() on a non-object in /home/forcjuny/public_html/newsletter.php on line 20”
1. W.S. Toh
  January 22, 2020 at 2:56 am
  
  Download and try again… Small changes done to the newsletter library.
MIchael Perdun
January 7, 2020 at 1:50 am

This is GREAT SCRIPT! THANK YOU! I have been looking for this script for a long time. Worked with a few tweaks to my previous table and it works well on my local host. : ) I will be visiting your website often.

Bulk Send Email Newsletters with PHP MySQL – Free Code Download