PHP, Email and Cron

Question

Let me rephrase my question, I have a mysql database that is holding emails to be sent, on a shared host. I would like to run a cron job that will read the database and sent out any messages in the database every 10 minutes or so.

Now my question is, what is the best way with php to read my database and send out the emails in small batched so that I don't overwhelm the shared host.

Answer 1

I would leave the throttling to the email server. Ie, run an email server locally, and have your PHP code relay all these messages to that. Then configure the email server itself to only send out at a certain rate.

Answer 2

Assuming the use of PDO, and making some accommodation for not knowing your schema, it might look something like this:

$dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', 'dbuser', 'dbpass');
$msgh = $dbh->prepare('SELECT subject, body from message where listname = :listname');
$msgh->bindParam(':listname', $listname, PDO::PARAM_STR);
$msgh->execute();
$msg = $msgh->fetch(PDO::FETCH_ASSOC);

$usrh = $dbh->prepare('SELECT recipient from userlist where is_subscribed_to = :listname');
$usrh->bindParam(':listname', $listname, PDO::PARAM_STR);
$usrh->execute();
while ($recipient = $usrh->fetch(PDO::FETCH_ASSOC)) {
  mail($recipient, $msg['subject'], $msg['body']);
  if ($over_throttle) {
    sleep(THROTTLE_SLEEP_SECONDS);
    $over_throttle = 0;
  }
  ++$over_throttle;
}

As for 'prewritten', you might take a look at phplist.

Answer 3

Well I came up with this solution similar to the PDO one. Are there any unforeseen problems with running this as a cron job?

<?php
$con = mysql_connect("localhost","root","123456");
$throttle = 0;
$batch = 50;
$pause = 10; // seconds

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("maildb", $con);

// Message Table
$MSGresult = mysql_query("SELECT * FROM msgs");

// User Table
$USERresult = mysql_query("SELECT * FROM members");

while($MSGrow = mysql_fetch_array($MSGresult))
  {
    while($USERrow = mysql_fetch_array($USERresult))
     {
       mail($USERrow['email'],$MSGrow['subject'],$MSGrow['body']);
       $throttle += 1;
       if ($throttle > $batch ) { sleep($pause); $throttle = 0;} 
     }
    mysql_data_seek($USERresult,0);
  }

mysql_close($con);
?>