asked on

PHP output buffering

Hi

I am running a PHP script that generate a list of variables, merges them into a email template and sends it through Manrdill Mailchimp's transactional email system).

The process might loop for several thousand iterations to generate all the emails that need sending.

The script that I am using:

//generate data for all records
while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
//populate some additional tables with info for each record
//merge each record into the email template
//send email
//update record to show that it has been emailed
}

This runs through all the records then snds the emails/updates the table etc. Depending on the number of records, this can take quite some time to process.

I have extended the PHP timeout and max-memory size to cope, but would prefer a better solution.

Would the ob_start() / ob_get_clean() functions help by processing each record individually and not storing everything up in memory, potentially over loading the buffer/memory and then outputting everything.

If someone can advise if this is a good idea or not and how I should implement it, I'd be very grateful.

Thank you in advance

Julian Hansen

Can't see how ob_Start will help all that does is buffers the output

If you are processing each mail independently - other than the memory consumed by the SQL recordset can't see how you would require any more for multiple than you need for one.

help by processing each record individually and not storing everything up in memory, potentially over loading the buffer/memory and then outputting everything

Can you explain your thinking here a bit more - I don't understand if you are sending off emails one by one where the buffering is happening?

Richard Lloyd

ASKER

Hi.

Maybe I didn't understand what the buffer was!

What seems to happen is that when I run my script, it completes the entire "while" loop, maybe several thousand times, then once complete, sends all the emails.

Therefore I assume that all the records etc and template info etc must be held in memory before the email calls are actually made.

I have tested this by adding a timestamp at the start of the script and at the end, the difference is only seconds, whilst the script can run for minutes.

What I want the script to do is merge a record with the template, call the mandrill API to send it, send the email, update the database and move on, clearing the "queue" after processing each record.

Hopefully this will mean that the script could run over many thousand records without running out of resources.

Does this explain it better?

Julian Hansen

The loop as you have it appears to do this but obviously the specifics are what is important.

What is your code to send the email?

Richard Lloyd

ASKER

The code for the whole script is as follows:

<?php 
include "config.php";

function nulldate($var){
if(is_a($var, 'datetime')){
$val= $var->format('d/m/Y');
} else {
$val='';
} 
return $val;
};
function generateRandomString($length = 10) {
$characters = '0123456789';
$randomString = '';
for ($i = 0; $i < $length; $i++) {
$randomString .= $characters[rand(0, strlen($characters) - 1)];
}
return $randomString;
}
echo "<h1>Send Mail Queue</h1>";
echo "<p>".date("Y-m-d H:i:s")."</p>";
$sql="select [id]
,[companyid]
,[accountno]
,[memberno]
,[cardno]
,[title]
,[firstname]
,[surname]
,[email]
,[date]
,[template]
,[emailtitle]
,[dailypoints]
,[birthday]
,[birthmonth]
,[lastvisit]
,[expirydate]
,[mandrill_api]
,[mailfrom]
,[mailfromname]
,[sentid]
,[sentdate]
,[campaignid]
,[vouchertype1]
,[voucherid1]
,[voucherbarcode1]
,[pin1]
,[vouchertype2]
,[voucherid2]
,[voucherbarcode2]
,[pin2]
from [tblmailqueue] where sentid is null" ;
$params=array();
$result=sqlsrv_query($conn, $sql, $params);
include "error.php";
$i=0;
while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
$i=$i+1;
$name=$row['title']." ".$row['firstname']." ".$row['surname'];
$email=$row['email'];
$companyid=$row['companyid'];
$accountno=$row['accountno'];
$memberno=$row['memberno'];
$cardno=$row['cardno'];
$template = $row['template'];
$emailtitle = $row['emailtitle'];
$mandrill_api_key = $row['mandrill_api'];
$from = $row['mailfrom'];
$fromname = $row['mailfromname'];
$id = $row['id'];

$expirydate=nulldate($row['expirydate']);
$dailypoints = $row['dailypoints'];

$campaignid = $row['campaignid'];
$vouchertype1 = $row['vouchertype1'];
$vouchertype2 = $row['vouchertype2'];
$voucherbarcode1 = $row['voucherbarcode1'];
$voucherbarcode2 = $row['voucherbarcode2'];
$pin1 = generateRandomString(4);
$pin2 = generateRandomString(4);

if(strlen($vouchertype1)>0){
$sql="set dateformat dmy insert into tblvoucherspromo (
companyid
,accountno
,memberno
,cardno
,expirydate
,vouchertype
,voucherbarcode
,campaignid
,pin
,issueddate
)
values(
?
,?
,?
,?
,?
,?
,?
,?
,?
,getdate())
; SELECT SCOPE_IDENTITY() " ;
$params=array($companyid ,$accountno ,$memberno ,$cardno ,$expirydate ,$vouchertype1, $voucherbarcode1, $campaignid, $pin1);
$result1=sqlsrv_query($conn, $sql, $params);
include "error.php";
//get id
sqlsrv_next_result($result1); 
sqlsrv_fetch($result1); 
$vid=sqlsrv_get_field($result1, 0);

$vid=str_pad($vid,5,"0",STR_PAD_LEFT);
$voucherid1="999".$vid;
}

if(strlen($vouchertype2)>0){
$sql="set dateformat dmy insert into tblvoucherspromo (
companyid
,accountno
,memberno
,cardno
,expirydate
,vouchertype
,voucherbarcode
,campaignid
,pin
,issueddate
)
values(
?
,?
,?
,?
,?
,?
,?
,?
,?
,getdate())
; SELECT SCOPE_IDENTITY() " ;
$params=array($companyid ,$accountno ,$memberno ,$cardno ,$expirydate ,$vouchertype2, $voucherbarcode2, $campaignid, $pin2);
$result2=sqlsrv_query($conn, $sql, $params);
include "error.php";
//get id
sqlsrv_next_result($result2); 
sqlsrv_fetch($result2); 
$vid=sqlsrv_get_field($result2, 0);

$vid=str_pad($vid,5,"0",STR_PAD_LEFT);
$voucherid2="999".$vid;
}

require_once('mandrill/src/Mandrill.php');
$mandrill = new Mandrill($mandrill_api_key);

$message = array(
'to' => array(
array(
'email' => $email,
'name' => $name,
'type' => 'to'
)
),
'from_email'=> $from,
'from_name'=> $fromname,
'reply-to'=> $replyto,
'important' => false,
'merge' => true,
'merge_language' => 'handlebars',
'subject' => $emailtitle,
'global_merge_vars' => array(
array(
'name' => 'name',
'content' => $name
),
array(
'name' => 'accountno',
'content' => $accountno			
),
array(
'name' => 'memberno',
'content' => $memberno			
),
array(
'name' => 'cardno',
'content' => $cardno			
),
array(
'name' => 'email',
'content' => $email			
),
array(
'name' => 'expirydate',
'content' => $expirydate			
),
array(
'name' => 'dailypoints',
'content' => $dailypoints			
),
array(
'name' => 'voucherid1',
'content' => $voucherid1			
),
array(
'name' => 'pin1',
'content' => $pin1			
),
array(
'name' => 'voucherid2',
'content' => $voucherid2
),
array(
'name' => 'pin2',
'content' => $pin2			
)

),
);
$resultemail = $mandrill->messages->sendTemplate($template, $template_content, $message);
echo $i." - ".$id." - ".$companyid."  - ".$template." - ";
print_r($resultemail);
echo "<br/>";

$messageid=$resultemail[0][_id];
$status=$resultemail[0]['status'];
$reason=$resultemail[0]['reject_reason'];

$sql="update tblmailqueue set sentstatus=?, sentreason=?, sentdate=getdate(), sentid=? where id=?";
$params=array($status, $reason, $messageid, $id);
sqlsrv_query($conn, $sql, $params);
include "error.php";
}
echo "<p>".date("Y-m-d H:i:s")."</p>";

Open in new window

I am looking for a way to "dribble out" the emails, and not wait for the processing to finish and then have a "tsumani" of emails being sent!

Julian Hansen

A suggestion. Well formatted code is critical to successful maintenance and debugging. It is even more important when you post code here that it is formatted correctly - especially if it is complex / long. The time it takes us to reformat code so we can see what is going on can be a major deterrent to answering the question. Just a suggestion to help you get better results on this forum.

Why are you including the mandril library in your loop

	require_once('mandrill/src/Mandrill.php');
	$mandrill = new Mandrill($mandrill_api_key);

Open in new window

This should be done outside of the while loop.

Julian Hansen

For reference I include your code reformmatted here

<?php 
include "config.php";

function nulldate($var){
  if(is_a($var, 'datetime')){
    $val= $var->format('d/m/Y');
  } else {
    $val='';
  } 
  return $val;
};
function generateRandomString($length = 10) {
  $characters = '0123456789';
  $randomString = '';
  for ($i = 0; $i < $length; $i++) {
    $randomString .= $characters[rand(0, strlen($characters) - 1)];
  }
  return $randomString;
}
echo "<h1>Send Mail Queue</h1>";
echo "<p>".date("Y-m-d H:i:s")."</p>";
$sql="select [id]
,[companyid]
,[accountno]
,[memberno]
,[cardno]
,[title]
,[firstname]
,[surname]
,[email]
,[date]
,[template]
,[emailtitle]
,[dailypoints]
,[birthday]
,[birthmonth]
,[lastvisit]
,[expirydate]
,[mandrill_api]
,[mailfrom]
,[mailfromname]
,[sentid]
,[sentdate]
,[campaignid]
,[vouchertype1]
,[voucherid1]
,[voucherbarcode1]
,[pin1]
,[vouchertype2]
,[voucherid2]
,[voucherbarcode2]
,[pin2]
from [tblmailqueue] where sentid is null" ;
$params=array();
$result=sqlsrv_query($conn, $sql, $params);
include "error.php";
$i=0;
while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
  $i=$i+1;
  $name=$row['title']." ".$row['firstname']." ".$row['surname'];
  $email=$row['email'];
  $companyid=$row['companyid'];
  $accountno=$row['accountno'];
  $memberno=$row['memberno'];
  $cardno=$row['cardno'];
  $template = $row['template'];
  $emailtitle = $row['emailtitle'];
  $mandrill_api_key = $row['mandrill_api'];
  $from = $row['mailfrom'];
  $fromname = $row['mailfromname'];
  $id = $row['id'];

  $expirydate=nulldate($row['expirydate']);
  $dailypoints = $row['dailypoints'];

  $campaignid = $row['campaignid'];
  $vouchertype1 = $row['vouchertype1'];
  $vouchertype2 = $row['vouchertype2'];
  $voucherbarcode1 = $row['voucherbarcode1'];
  $voucherbarcode2 = $row['voucherbarcode2'];
  $pin1 = generateRandomString(4);
  $pin2 = generateRandomString(4);

  if(strlen($vouchertype1)>0){
    $sql="set dateformat dmy insert into tblvoucherspromo (
    companyid
    ,accountno
    ,memberno
    ,cardno
    ,expirydate
    ,vouchertype
    ,voucherbarcode
    ,campaignid
    ,pin
    ,issueddate
    )
    values(
    ?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,getdate())
    ; SELECT SCOPE_IDENTITY() " ;
    $params=array($companyid ,$accountno ,$memberno ,$cardno ,$expirydate ,$vouchertype1, $voucherbarcode1, $campaignid, $pin1);
    $result1=sqlsrv_query($conn, $sql, $params);
    include "error.php";
    //get id
    sqlsrv_next_result($result1); 
    sqlsrv_fetch($result1); 
    $vid=sqlsrv_get_field($result1, 0);

    $vid=str_pad($vid,5,"0",STR_PAD_LEFT);
    $voucherid1="999".$vid;
  }
  if(strlen($vouchertype2)>0){
    $sql="set dateformat dmy insert into tblvoucherspromo (
    companyid
    ,accountno
    ,memberno
    ,cardno
    ,expirydate
    ,vouchertype
    ,voucherbarcode
    ,campaignid
    ,pin
    ,issueddate
    )
    values(
    ?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,?
    ,getdate())
    ; SELECT SCOPE_IDENTITY() " ;
    $params=array($companyid ,$accountno ,$memberno ,$cardno ,$expirydate ,$vouchertype2, $voucherbarcode2, $campaignid, $pin2);
    $result2=sqlsrv_query($conn, $sql, $params);
    include "error.php";
    //get id
    sqlsrv_next_result($result2); 
    sqlsrv_fetch($result2); 
    $vid=sqlsrv_get_field($result2, 0);

    $vid=str_pad($vid,5,"0",STR_PAD_LEFT);
    $voucherid2="999".$vid;
  }

  require_once('mandrill/src/Mandrill.php');
  $mandrill = new Mandrill($mandrill_api_key);

  $message = array(
    'to' => array(
      array(
        'email' => $email,
        'name' => $name,
        'type' => 'to'
      )
    ),
    'from_email'=> $from,
    'from_name'=> $fromname,
    'reply-to'=> $replyto,
    'important' => false,
    'merge' => true,
    'merge_language' => 'handlebars',
    'subject' => $emailtitle,
    'global_merge_vars' => array(
      array(
        'name' => 'name',
        'content' => $name
      ),
      array(
        'name' => 'accountno',
        'content' => $accountno      
      ),
      array(
        'name' => 'memberno',
        'content' => $memberno      
      ),
      array(
        'name' => 'cardno',
        'content' => $cardno      
      ),
      array(
        'name' => 'email',
        'content' => $email      
      ),
      array(
        'name' => 'expirydate',
        'content' => $expirydate      
      ),
      array(
        'name' => 'dailypoints',
        'content' => $dailypoints      
      ),
      array(
        'name' => 'voucherid1',
        'content' => $voucherid1      
      ),
      array(
        'name' => 'pin1',
        'content' => $pin1      
      ),
      array(
        'name' => 'voucherid2',
        'content' => $voucherid2
      ),
      array(
        'name' => 'pin2',
        'content' => $pin2      
      )
    ),
  );
  
  $resultemail = $mandrill->messages->sendTemplate($template, $template_content, $message);
  echo $i." - ".$id." - ".$companyid."  - ".$template." - ";
  print_r($resultemail);
  echo "<br/>";

  $messageid=$resultemail[0][_id];
  $status=$resultemail[0]['status'];
  $reason=$resultemail[0]['reject_reason'];

  $sql="update tblmailqueue set sentstatus=?, sentreason=?, sentdate=getdate(), sentid=? where id=?";
  $params=array($status, $reason, $messageid, $id);
  sqlsrv_query($conn, $sql, $params);
  include "error.php";
}
echo "<p>".date("Y-m-d H:i:s")."</p>";

Open in new window

Note you can probably rationalise the voucher promo lookups into one query. As you are setting $vid in both the second will supersede the first if $vouchertype2 is not empty (BTW we usually use

if (!empty($vouchertype2))

Open in new window

in place of

if(strlen($vouchertype2)>0)

Open in new window

What you do is set generic values for
$companyid ,$accountno ,$memberno ,$cardno ,$expirydate ,$vouchertype2, $voucherbarcode2, $campaignid, $pin2
i.,e.

if (!empty($vouchertype1) {
   $companyid=$row['companyid'];
   ...
   $vouchertype = $vouchertype1;
   $voucherbarcode = $row['voucherbarcode1'];
   $campaignid = $row['campaignid'];
   $pin = generateRandomString(4);
}
else if (!empty($vouchertype2)) {
   $companyid=$row['companyid'];
   ...
   $vouchertype = $vouchertype2;
   $voucherbarcode = $row['voucherbarcode2'];
   $campaignid = $row['campaignid'];
   $pin = generateRandomString(4);
}

Open in new window

Now do your query here with the generics rather than a duplicate query

Richard Lloyd

ASKER

Thanks. I'll have a go and get back to you.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.