How to integrate Mail Queue with CodeIgniter

[eluser]iamrendell[/eluser]
Hi! I am using Mail Queue for sending batch notifications to the users of my system. I'm just wondering how I may be able to implement this with CI... and where do you place the Mail Queue folder?

Badly in need of help... =D

[eluser]Flemming[/eluser]
This answer may be totally unhelpful but I'll say it anyway as I notice you haven't had any other suggestions! :-(

I recently wrote a simple mail queue using CI. A cron job runs every 5 mins calling my queue controller. That controller loops through the mailing list mysql table starting at row x and returning y results. For each result it sends out an email. When it has finished its batch it updates the 'startFrom' field in the mysql table so that next time it runs (5 mins later) it picks out the next batch or records.

Each time it runs it checks to see if it's sent them all yet.

It's pretty easy to do - if you think that would be a good solution for you I'd be happy to post some example code!

[eluser]sensimevanidus[/eluser]
[quote author="flemming" date="1223995166"]This answer may be totally unhelpful but I'll say it anyway as I notice you haven't had any other suggestions! :-(

I recently wrote a simple mail queue using CI. A cron job runs every 5 mins calling my queue controller. That controller loops through the mailing list mysql table starting at row x and returning y results. For each result it sends out an email. When it has finished its batch it updates the 'startFrom' field in the mysql table so that next time it runs (5 mins later) it picks out the next batch or records.

Each time it runs it checks to see if it's sent them all yet.

It's pretty easy to do - if you think that would be a good solution for you I'd be happy to post some example code![/quote]I'd appreciate some example code

[eluser]Flemming[/eluser]
sorry for the delay! OK, here's some code. It's a cut-down version of my original so it's untested and I apologise if it doesn't work straight 'out of the box' OR if there are some GLARING errors in it!

First the sql - 2 tables:

Code:

CREATE TABLE `newsletters` (
  `uid` int(11) NOT NULL auto_increment,
  `created` datetime NOT NULL,
  `sent` datetime NOT NULL,
  `subject` varchar(100) default NULL,
  `html` text,
  `queued` tinyint(1) NOT NULL default '0',
  `startNum` int(11) NOT NULL,
  PRIMARY KEY  (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;


CREATE TABLE `newsletter_subscribers` (
  `uid` int(11) NOT NULL auto_increment,
  `email` varchar(100) NOT NULL,
  `subscriberRef` varchar(32) NOT NULL,
  `active` tinyint(1) NOT NULL,
  PRIMARY KEY  (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

then the model :

Code:

<?php

class newsletter_model extends Model {

  function newsletter_model()
  {
    parent::Model();
  }
    
        function getQueuedNewsletters()
    {
        $this->db->where('queued', '1');    
        return $this->db->get('newsletters');
    }
    

    function getFirstQueuedMailing(){
        $this->db->where('queued', '1');
        $this->db->order_by('uid', 'asc');
        return $this->db->get('newsletters',1)->row();
    }    

    function getBatchNewsletterSubscribers($startNum,$quant)
    {
        $this->db->where('active', '1');
        $this->db->orderby('uid', 'asc');
        return $this->db->get('newsletter_subscribers',$quant,$startNum);
        // remember! codeigniter does LIMIT the opposite way round : numRows, startRow
    }
    
    function countNewsletterSubscribers()
    {
        $query = $this->db->get('newsletter_subscribers');
        return $query->num_rows();
    }
        
    function updateNewsletterStartNum($newsletterID, $quant, $totalSubscribers)
    {
        $this->db->where('uid', $newsletterID);
        $this->db->set('startNum', 'startNum+'.$quant, FALSE);
        $this->db->update('newsletters');
        
        if($this->getNewsletter($newsletterID)->startNum >= $totalSubscribers)
        {
            $currentTime = date("Y-m-d H:i:s");
            $this->db->where('uid', $newsletterID);
            $this->db->set('startNum', '0', FALSE);
            $this->db->set('queued', '0', FALSE);
            $this->db->set('sent', $currentTime);
            $this->db->update('newsletters');
        }
    }
}

?>

and finally the controller:

Code:

<?php

class SendNewsletter extends Controller {
    
    function SendNewsletter()    {
        parent::Controller();
    }
    
    function index(){
        
        $this->load->model('newsletter_model');
        $this->load->library('email');
        
        $quant = 10; // number of newsletters to send each time
        
        // are there any newsletters queued?
        if($this->newsletter_model->getQueuedNewsletters()->num_rows > 0)
        {
            // get the ID of the FIRST item in queue
            $newsletterData = $this->newsletter_model->getFirstQueuedMailing();
            
            $newsletterID = $newsletterData->uid;

            $subscribers = $this->newsletter_model->getBatchNewsletterSubscribers($newsletterData->startNum,$quant);
            
            $totalSubscribers = $this->newsletter_model->countNewsletterSubscribers();
            
            foreach ($subscribers->result() as $address) {
            
                $this->email->clear(); // clear anything that's already here
                
                $config['mailtype'] = 'html';
            $config['charset'] = 'utf-8';
            $this->email->initialize($config);
                
                $this->email->to($address->email); // the email address of the subscriber
                $this->email->from('[email protected]');
                $this->email->subject($newsletterData->subject);
                $this->email->message($newsletterData->html);
                $email_sent = $this->email->send();
            }
            
            // update the newsletter table with the new startNum    
            $updateStartNum = $this->newsletter_model->updateNewsletterStartNum($newsletterID, $quant, $totalSubscribers);
        
        } // end check for queued newsletters
    }
    
}

The theory is it will send $quant emails every time the controller is called (assuming there is a queued newsletter, so populate the mysql tables and make sure you flag a newsletter as queued.

Let me know how you get on! :-)

[eluser]sensimevanidus[/eluser]
Thanks for the example code. I took a quick look at these and they look fine. You could also use multiple processes for mass mailing. Here's an article about forking in PHP supported with a simple example. Forking can boost your script's performance.

[eluser]iamrendell[/eluser]
Wow! :bug: I understand the concept and it's indeed simple. I've been trying to implement Mail Queue in CI for days already but still, no success. Thanks by the way, I'm sure this will be a big help!

Actually, I haven't tried using cron job. Could you tell me the steps to call the controller? THANKS! THANKS! THANKS! THANKS! THANKS! THANKS! THANKS! THANKS! THANKS! THANKS!

[eluser]Flemming[/eluser]
if you have access to crontab on your linux host then you can try something like this:

open the crontab with the command crontab -e

then put this as the first line

Code:

*/5 * * * * lynx -dump http://www.yoursite.co.uk/script_to_cron.php > /dev/null

which will use the lynx browser to run your php script every 5 minutes. There are other ways - just search for crontab tutorials!

If your host does not allow you to access the crontab then you can use something like:

Code:

http://www.webbasedcron.com/

(I can recommend this one - I researched web-based cron services and this one seems the best)

Hope that helps!

Oh and by the way - there's a function missing from the example code I posted ...

In the model, it refers to

Code:

getNewsletter($newsletterID)

so you need to add this function to your model:

Code:

function getNewsletter($newsletterID)
    {        
        $this->db->where('uid', $newsletterID);
        return $this->db->get('newsletters')->row();
    }

just to get the newsletter data from the 'newsletters' table!

[eluser]iamrendell[/eluser]
Good job! WOOOOW! :bug: Got it... Now, I don't have to use PEAR's Mail Queue for my batch email function. Thanks to you, flemming and CodeIgniter!

[eluser]Flemming[/eluser]
Pleased I could help! Have you actually got it up and running?

[eluser]iamrendell[/eluser]
Ooops! Sorry for the late reply. Yeah, it's up and running now. Not a sweat! hihi.. thanks again!