Send Emails using Crontab for Your Web Applications

In your web applications, sometimes you would need to send users tons of emails. You can send them real time when users e.g. press a send button. But this is generally not a good idea as it will be synchronous (page delayed until mail is actually sent) and in some sense it will increase your server’s work load. And the scalability is limited due to possible peak time a huge number of mails to send, that will overload the server.

A better idea is to store these emails (to send) in a table and then the server schedules them to be sent every few minutes. First we can define a SQL tables to store these emails:

-- phpMyAdmin SQL Dump
-- version 4.2.6
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 05, 2015 at 01:24 PM
-- Server version: 5.5.40-0ubuntu0.14.04.1
-- PHP Version: 5.5.9-1ubuntu4.5

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `zlai`
--

-- --------------------------------------------------------

--
-- Table structure for table `mails`
--

CREATE TABLE IF NOT EXISTS `mails` (
`id` bigint(32) unsigned NOT NULL,
  `sender` varchar(255) NOT NULL,
  `dest` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `sent` tinyint(1) NOT NULL,
  `ts` datetime NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `mails`
--
ALTER TABLE `mails`
 ADD PRIMARY KEY (`id`), ADD KEY `sent` (`sent`), ADD KEY `ts` (`ts`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `mails`
--
ALTER TABLE `mails`
MODIFY `id` bigint(32) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

sql-mails-table

Then, we can replace actually sending code by inserting ‘mails’ to the table.

    $ts = date('Y-m-d h:i:s');
    $sender = mysql_real_escape_string($_POST['user']); 
    $subject = mysql_real_escape_string($_POST['subject']);
    $content = mysql_real_escape_string($_POST["content"]);    
    $query = "
      insert into `mails` set
          `sender` = '$sender',
          `dest` = '[email protected]',
          `subject` = '$subject',
          `content` = '$content',
          `ts` = '$ts',
          `sent` = 0  
    ";
    // push to database for sending later (asynchronous)
    $result = mysql_query($query);

    $ts = date('Y-m-d h:i:s');
    $sender = mysql_real_escape_string($_POST['user']);	
    $subject = mysql_real_escape_string($_POST['subject']);
    $content = mysql_real_escape_string($_POST["content"]);    
    $query = "
      insert into `mails` set
          `sender` = '$sender',
          `dest` = '[email protected]',
          `subject` = '$subject',
          `content` = '$content',
          `ts` = '$ts',
          `sent` = 0  
    ";
    // push to database for sending later (asynchronous)
    $result = mysql_query($query);

Now, we create a process_email.php that gets the list of un-sent emails and send them one by one via the PHPMailer class.

  set_time_limit(600);
  $link = mysql_connect("localhost", "user", "password") or die(mysql_error());
                              
  mysql_select_db('database') or die(mysql_error());
  
  mysql_query("SET NAMES 'utf8'");
  mysql_query("SET CHARACTER SET utf8");
  mysql_set_charset("utf-8", $link);
    
  // get list of unsent emails
  // we limit to 100 mails per time
  $query = "select * from `mails` where `sent` = 0 limit 100";
  
  $result = mysql_query($query) or die(mysql_error());
  require("PHPMailerAutoload.php");
  $mailer = new PHPMailer();
  $mailer->CharSet = "UTF-8";
  $mailer->IsHTML(true);
  
  // process one by one
  while ($row = mysql_fetch_array($result)) {
    $sender = $row['sender'];
    $dest = $row['dest'];
    $subject = $row['subject'];
    $content = $row['content'];
    $id = $row['id']; 
    $mailer->From = $sender;
    $mailer->FromName = $sender;
    $mailer->ClearAllRecipients();
    $mailer->AddAddress($dest, $dest);
    $mailer->Subject = $subject;
    $mailer->Body = $content;
    if ($mailer->Send()) {
      echo "$sender - OK! 
";
      // mark as sent!
      $query = "update `mails` set `sent` = 1 where `id` = $id limit 1";
      mysql_query($query) or die(mysql_error());
    } else {
      echo "$sender - failed! $mailer->ErrorInfo 
";
    } 
  }

  set_time_limit(600);
  $link = mysql_connect("localhost", "user", "password") or die(mysql_error());
                              
  mysql_select_db('database') or die(mysql_error());
  
  mysql_query("SET NAMES 'utf8'");
  mysql_query("SET CHARACTER SET utf8");
  mysql_set_charset("utf-8", $link);
    
  // get list of unsent emails
  // we limit to 100 mails per time
  $query = "select * from `mails` where `sent` = 0 limit 100";
  
  $result = mysql_query($query) or die(mysql_error());
  require("PHPMailerAutoload.php");
  $mailer = new PHPMailer();
  $mailer->CharSet = "UTF-8";
  $mailer->IsHTML(true);
  
  // process one by one
  while ($row = mysql_fetch_array($result)) {
    $sender = $row['sender'];
    $dest = $row['dest'];
    $subject = $row['subject'];
    $content = $row['content'];
    $id = $row['id']; 
    $mailer->From = $sender;
    $mailer->FromName = $sender;
    $mailer->ClearAllRecipients();
    $mailer->AddAddress($dest, $dest);
    $mailer->Subject = $subject;
    $mailer->Body = $content;
    if ($mailer->Send()) {
      echo "$sender - OK! 
";
      // mark as sent!
      $query = "update `mails` set `sent` = 1 where `id` = $id limit 1";
      mysql_query($query) or die(mysql_error());
    } else {
      echo "$sender - failed! $mailer->ErrorInfo 
";
    } 
  }

Finally, we need to set up a script e.g. del-email.php that cleans up the sent emails to prevent tables getting bigger and bigger (e.g. once per day).

  set_time_limit(600);
  mysql_connect("localhost", "user", "password") or die(mysql_error());
                              
  function run_query($query) {
    echo $query;
    mysql_query($query) or die(mysql_error());
    echo " ***OK!*** \n";
  }         
  
  mysql_select_db('database') or die(mysql_error());  
  $query = 'delete from `mails` where `sent` = 1 limit 1';  
  run_query($query);

  set_time_limit(600);
  mysql_connect("localhost", "user", "password") or die(mysql_error());
                              
  function run_query($query) {
    echo $query;
    mysql_query($query) or die(mysql_error());
    echo " ***OK!*** \n";
  }         
  
  mysql_select_db('database') or die(mysql_error());  
  $query = 'delete from `mails` where `sent` = 1 limit 1';  
  run_query($query);

Use crontab -e to add these two jobs accordingly e.g. check emails to send every minute and clean sent emails every day. In this way, you protect the server to make the email-sending asynchronously and you may obtain the statistics (e.g. total emails per day) if you like.

The scalability is now enhanced by employing such asynchronous method for sending a huge amount of emails.

–EOF (The Ultimate Computing & Technology Blog) —

GD Star Rating
a WordPress rating system

836 words
Last Post: Agile Development - Sprint Board
Next Post: BASH Script to Get Rid of IptabLex Virus

The Permanent URL is: Send Emails using Crontab for Your Web Applications

Algorithms, Blockchain and Cloud

Send Emails using Crontab for Your Web Applications

One Response

Leave a Reply

Related posts:

One Response

Leave a Reply