Join 132,462 PHP Programmers for FREE! Get instant access to thousands of PHP experts, tutorials, code snippets, and more! There are 1,369 people online right now. Registration is fast and FREE... Join Now!
I've just got PEAR Mail_Queue working nicely now, partly thanks to the tips here (didn't know to use it before).
But - from what I understand, the body of the mail message has to be stored in the mail_queue table in the database for each recipient. Is there any way to get the mail_queue to read the mail body from another table, thus avoiding duplication? As it stands, my queueing table is turning into a monster, quite unnecessarily it seems. Since there are 500 plus recipients, if each receives a mail that's 100kb (multipart sometimes), I'm having problems even with the $mail_queue->put() here as the content is quite large. Perhaps this is more of a MySQL insert issue. I have to admit to pretty beginner status with all this. Even if I can get a fix for the large inserts, I would have thought calling the body from another table would make sense to avoid duplication?
Am I missing something obvious here, or will i have to find another workaround. Currently I'm adding just a batch to the queue at a time, but that seems like I'm having to queue the queue!
Calling sendMailsInQueue() with an integer representing the maximum number to send in that batch will help. Remember that sent messages are flagged, so simply setting this to a reasonable limit and then calling this script periodically until all messages are cleared should do the trick.
The Mail_Queue doesn't have to keep messages it sends, either. I use the queue to keep track of system-generated messages, which usually come in handfulls throughout the course of a day. For bulk mail, if you do want to use the queue you might also want to periodically clean it out, or just call it without the flag that tells the application to keep the message in the table after it has been sent. By default, the mail queue will delete sent messages as soon as they have gone out. Might be better for your application.
I've been using sendMailsInQueue() successfully to send the mails once they're in the queue, and yes, I found the delete after send boolean, thanks - that's all working beautifully - I was more concerned with the process of adding the mails to the queue in the first place.
The queue table is huge because it repeats the mail body so many times, once per email recipient. So I was just wondering if it was possible to tweak it so the body is called by a reference from a further database table, saving Mb of duplication, and preventing slowdowns / script timeouts in big INSERTS to the queue table.
So I have two issues really:
1. As above, is it possible to reference a different table for the message body rather than copy it into each and every email recipient's mail body in the queue table?
2. If not, I'm unsure of how to insert large quantities of data at a time from a PHP script. I seem to be cut off at about 1.5Mb of data - probably the script timing out? This is happening because I need to insert the whole body of the mail into the mail queue table each time, hence question 1. above! My workaround is to queue the insert process as well, inserting batches of data at a time, but there's probably something obvious I'm missing here.
1. grab html and text-format message from a 'saved_mail' table, each is possibly 50Kb in size.
2. Loop through list of subscribers in 'member' table - currently 500 Then check whether member has subscribed to html or text-only, or is AOL user, and provide MIME mail, or text mail accordingly.
CODE
$email_sql = "SELECT idmember, username, mailformat FROM member WHERE status = \"live\" ORDER BY idmember"; $result =& $db->query($email_sql); while ($row = $result->fetchRow()) { $memberid = $row[0]; $recipient = $row[1]; $mailformat = $row[2]; if (($mailformat == "text") || (stristr($recipient, 'aol.com'))) { $body = $text; $from = 'info@something.com'; $headers['From'] = 'Me <info@something.com>'; $headers['Subject'] = 'Lists for '.$formatted_date; $mail_queue->put($from, $recipient, $headers, $body, 0, TRUE); } else { $mime = new Mail_mime($crlf = "\n"); // version with CRLF set to \n works on this server $mime->setTXTBody($text); $mime->setHTMLBody($html); $mimebody = $mime->get(); $hdrs['From'] = 'Me <info@something.com>'; $hdrs['Subject'] = 'List for '.$formatted_date; $mimeheaders = $mime->headers($hdrs); $mail_queue->put($from, $recipient, $mimeheaders, $mimebody, 0, TRUE); } print ("($memberid) $recipient<br />"); } // end email loop
I did try the mailq function you provided, but there was no difference to the insert problem.
So you can see, the bodies aren't really huge - 100Kb of data per recipient, but the script terminated (no error reported) after about 100 records had been inserted in the queue.
If it's relevant, the server php.ini max_execution_time is set to 30, and I can't change it.
To get around the time limitations maybe you could have the script execute itself and die for every x number of emails it sends. Of course the you'd have to save your progress in a database so you don't start from the biginning.
Though maybe it's impossible, I don't know of an WScript.Shell equivalent for linux.
You could also increase the time/memory limits in the command-line version of the php script by editing its associated php.ini file. You might even try persistent MySQL connections or something wacky like that to speed up the inserts.
Hotsnoj - yes, I'll probably end up using another cron job here. The process is getting very complex, and I should probably go the whole hog and get myself mailman or something similar. This job is quite a custom one though.
Cyberscribe - I can't edit the php.ini file unfortunately, though I've read about changing max_execution_time in a .htaccess file and might try that.
PHP is also set up as a CGI, which apparently doesn't allow persistent connections. I also read up that INSERT DELAYED would help, but since it's the PEAR code that's doing the inserting, not me, I'd have to alter that, which I'm not comfortable with!
So - I'll make the suggestion to PEAR, and meantime have a stab at making the change myself - so the mail queue calls a second table containing the message body.
Thanks for all the tips, it's good to know I haven't missed something obvious!
sounds like he wants a seperate content table when the data is inserted(content all the same, from him, not the users), then when sending, add this content when send to all the recipiants.
Not hard to do, but it does require modifying the default Mail_Queue functionality. A key in the main table that specifies an ID that is the primary ID of the table that holds the common content should do it. Then it's just a simple join to pull out the field.
Thanks for the tips on modifying the Mail Queue functionality - I'll have a stab at it, and SpaceMan, you're exactly right, that's what I'm after.
Mail Queue does seem to do most of the work I want it to do, very efficiently, and I'm quite a newbie coder, so I'd rather start with a lot of the work done already
I'll have a go later and let you know if I get it to work.
12 Mar, 2005 - 01:28 PM
| 