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.

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

  • Create a database table to store the list of subscribers.
  • Create a PHP newsletter library that will bulk-send email newsletters to subscribers in batches.
  • Run the PHP script in the command line.

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.

 

 

TLDR – QUICK SLIDES

Fullscreen Mode – Click Here

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

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

 

QUICK NOTES

  • Create a database and import 1-database.sql.
  • Change the database settings in 2-lib-newsletter.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.

 

 

PHP BULK SEND EMAIL NEWSLETTER

All right, let us now get started with the example of how to send bulk email newsletters with PHP MYSQL.

 

STEP 1) NEWSLETTER DATABASE TABLE

1-database.sql
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

2-newsletter.php
<?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

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>

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

3b-process.php
<?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”.

 

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

 

TUTORIAL VIDEO

 

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!

16 thoughts on “4 Steps To Bulk Send Email Newsletters In PHP MySQL”

  1. Hello bro there is a question that how many maximum mails we send through this way.
    Can we send 10,000 or more mails using this strategy and code.
    because in my case I am trying to send 10,000 mails in a single day.
    Thanks regards,
    Asad

    1. I cannot guarantee anything. But technically, there’s no limit – The script will keep sending in batches as long as it is running.

    1. Don’t skip the basics, and learn what “SMTP” means – Regardless of programming language, you need an SMTP server to send out emails. What you are asking here is literally “how to work with raw SMTP to send email without PHP”.

      As above – https://code-boxx.com/fix-php-mail-not-working/ or if you mean the library called “PHPMailer” – https://code-boxx.com/send-email-php/

      P.S. If *that* is your intended question. Good luck studying the protocol, there are plenty of examples all over the Internet – https://en.wikipedia.org/wiki/Simple_Mail_Transfer_Protocol

  2. Hi!
    Thanks a lot! This helped me very much!
    Could you maybe add some instructions on how to attach a pdf document to the emails?
    Thanks!

  3. Hello,
    Thank you ! it works perfectly but
    I would like to pass the variable $sub[“email”] in the body of 3a-template.html like that of $sub[“name”] but I can’t, thank you for your help
    Florian

  4. 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. 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!

  5. 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. 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”.

  6. 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!

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

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

Leave a Comment

Your email address will not be published. Required fields are marked *