How to throttle a simple emailer script to limit messages sent per hour

[kuau]

Part 1
I have to send out an email ASAP to 158 people based on certain criteria. The SELECT query works fine when I run it against the database and returns the correct 158 records. To test it, I changed the email address to mine so I could see what gets sent, but I thought I would get 158 emails. Instead I received only one. It seems to be sending to the first record only.

I can't figure out why my WHILE loop is not looping through all 158 records. The includes should not be causing any problems as the email itself had all the variables filled in correctly. I've been looking at it too long. Can anyone see what I cannot? Thanks!


<?php
include('cr-connectdb.php');
require('class.phpmailer.php');

$sql = "SELECT * FROM `booking` WHERE `Book_Date` < '2010-6-30' ";
$result = mysql_query($sql,$connection) or die("Couldn't execute $sql query");

while($row = mysql_fetch_assoc($result)){
include('load-booking-variables.php');
$body = file_get_contents($Email_Alamo_Refund);
include('replace-email-variables.php');

$Subject = "Notice Regarding Your Hawaii Car Rental";
$emailaddress = $Email;
include('send-client-email.php');
}
?>

[kuau]

Part 2
I'm trying to create a simple custom emailer script that uses class.phpmailer.php to send an html email to client email addresses selected from a database table.

It is basically a SELECT command to retrieve the email addresses and then a WHILE loop that assembles and sends the email. As I do not know how many messages it sends out per hour by default, how do I throttle the sending to make sure it does not go beyond the host's limit? Is the proper way to use a SLEEP command inside the WHILE loop? Something like sleep(10);? I'm having trouble with the while loop sending to only the first record.

I would also like to have some way to test the script without sending any emails, such as echoing the email addresses it would send to once I enable the sending.

Any help would be greatly appreciated. Thanks. :)

[bluewalrus]

RE: Part 1
Not sure about the sending...


I would also like to have some way to test the script without sending any emails, such as echoing the email addresses it would send to once I enable the sending.

Comment out the mail() line and echo the values instead.

[kuau]

Thanks, bw. OK, I did that and it displayed only one email address. It confirmed what I suspected, that the WHILE loop is stopping after the first record in the array. There are actually 158 records returned from the real SQL command. Can you please tell me what is wrong with my while loop?



$sql = "SELECT * FROM `booking` WHERE `Book_Date` < '2010-6-30' AND `Book_Begin_Date` > '2010-8-31' ";
$result = mysql_query($sql,$connection) or die("Couldn't execute $sql query.");

while($row = mysql_fetch_array($result)){
include('load-booking-variables.php');
echo $emailaddress;l
}

[djr33]

RE: Part 1
Replace this line include('send-client-email.php'); with echo $emailaddress; and see what the results look like. That'll give you a list of the emails and you can see if it's all working at this stage at least.

[djr33]

RE: Part 2
It looks like you're matching a specific entry, using the "WHERE" clause like that, but I don't know how your database is setup. Is this the same topic/question as your other post? Should they be merged, or are you working on two similar scripts?

[kuau]

Hi Daniel: I was hoping you would show up. :)

Yes, you can combine the two threads as they are related. I am trying to write a generic emailer script that I can use in any situation. I have been using Pommo but it has a lot of bugs and limitations. The emailing I am trying to do right this moment is a specific application of the generic mailer. As there are only 158 records involved this time, I don't need to throttle it, but I want to know how to do it for the master script. So I am trying to do 2 things at once... create the general from the specific.

I simplified the code so that a logic error would stand out more. I have successfully used WHILE loops in situations where the results are displayed on a web page. This time the result is to send an email and write to a text file that the email was sent. It works for the first record only. I tried using mysql_fetch_assoc($result), and mysql_fetch_array($result), incrementing a counter, using array_push(), using array_push AND a counter... no matter what I do, it echoes only one email address. I can't figure out what I am doing wrong.

When I run the real SQL query against the database, it returns the correct 158 records. Shouldn't it echo 158 email addresses when I run the test? This is driving me nuts. [I edited the code above so that it is clearer what I am doing]

Thanks! :)

[djr33]

I've merged the two discussions.

There's nothing wrong with your syntax for the while loop. Assuming the simplified version didn't take any errors out, there is only one possibility (at least as far as I can see). It must be the included file... or at least let's eliminate that before continuing.

Comment out this line:
//include('load-booking-variables.php');

Then fix the echo so that it is echoing the right part of the array $row.
Example: echo $row['EMAIL'];


My guess is that there may be a naming conflict (possibly $row or $result) within the included file. Note that included files share scope with the main page and can overwrite a value. For this reason using functions can be a safe way to do it or you can make sure not to use the same names.


Now getting back to the issue of sending too many emails, the first question is how your host checks this. It is likely a limit of no more than X number of emails in Y time. But that could be no more than 3 per second or no more than 1,000 per month. The approach that makes the most sense will depend on this. If you find that out, you will know the best way to limit it.
sleep() is a good way to stop the emails from sending too often, but that may cause two problems:
1. The system may still notice that you are sending a lot of emails from one script or within a short period of time (for example 60 in 1 minute).
2. Most PHP configurations have a maximum execution time: with a large delay like 10 seconds and possibly a lot of records, you will VERY easily reach the default 30 second limit. It is possible to change this using php.ini, .htaccess or even just a command in the script, but this must be changed before you can use sleep() effectively unless it's for a very short amount of time. Note that to a computer, 100 miliseconds may be plenty of time, though if the host has a limit that will be more relevant. To us, it may seem like pausing 5 seconds is necessary, but for operations like this that is an eternity in digital time.

[kuau]

OK, I did that and the one email address disappeared. I doubt the include has an error because I use it elsewhere with no problem. Plus it did successfully pass the variable values to the email template for the one email that did get sent.

However, I did make some progress on it myself while I was waiting for you, by using 2 while loops. I got it to list the 158 email addresses and write them to the text file but there is an error message inbetween which increments by 10 each time. What does this mean?...


Warning: mysql_fetch_array(): 16 is not a valid MySQL result resource in /home1/mauiretr/public_html/_car/php/alamo-refund4.php on line 28
one@hawaiiantel.net

Warning: mysql_fetch_array(): 26 is not a valid MySQL result resource in /home1/mauiretr/public_html/_car/php/alamo-refund4.php on line 28
carlabbess@comcast.net

Warning: mysql_fetch_array(): 36 is not a valid MySQL result resource in /home1/mauiretr/public_html/_car/php/alamo-refund4.php on line 28
redtide101@aol.com

Warning: mysql_fetch_array(): 46 is not a valid MySQL result resource in /home1/mauiretr/public_html/_car/php/alamo-refund4.php on line 28
shortnsassy@hawaii.rr.com

Warning: mysql_fetch_array(): 56 is not a valid MySQL result resource in /home1/mauiretr/public_html/_car/php/alamo-refund4.php on line 28
jallen31@nc.rr.com

Warning: mysql_fetch_array(): 66 is not a valid MySQL result resource in /home1/mauiretr/public_html/_car/php/alamo-refund4.php on line 28
paramini@cox.net

Warning: mysql_fetch_array(): 76 is not a valid MySQL result resource in /home1/mauiretr/public_html/_car/php/alamo-refund4.php on line 28
teresaayala1@hotmail.com

Warning: mysql_fetch_array(): 86 is not a valid MySQL result resource in /home1/mauiretr/public_html/_car/php/alamo-refund4.php on line 28
teresaayala1@hotmail.com

Warning: mysql_fetch_array(): 96 is not a valid MySQL result resource in /home1/mauiretr/public_html/_car/php/alamo-refund4.php on line 28
lbaker0127@gmail.com


This is line 28: while($row = mysql_fetch_array($result)){

I can get rid of the error message by adding an "@" sign but would like to know why it is there. It doesn't do it for the first record. I'll see if the email still works. :)

[djr33]

It's hard to know exactly why that is happening, but I believe that means that $result is set to '16' and the other number at those times. That is... it should be the query result, but it isn't??
How are you counting every 10 records? That may be how this is working like that.

As for my example, it may be irrelevant now, but did you adjust "EMAIL" to the right field name? There's no reason, if that is changed, it shouldn't work. If it doesn't, it points to a bigger problem, I believe.

[kuau]

The mail messages work! Yea! But the odd thing is that 158 lines were written to the text file but I received only 83 emails. There were quite a few duplicate email addresses so it is possible that it sent only one per address, but I didn't put anyhting in the code to filter out dups. Any idea why this would happen?

[djr33]

Hmm... no. But first see my post (you might have missed it since we posted about the same time and your post made a new page). Let's see if we can fix that, then worry about the rest.
It is possible that some simply were delayed sending so you may receive them soon. It's hard to know. There's also some remote possibility, I assume, that your server automatically ignores duplicate requests as a spam or overload precaution, but that is a complete guess and there's no reason to assume that's the case. You could try it manually I suppose and see what happens.

[kuau]

Yes, I did adjust the Email variable as follows...


while($row = mysql_fetch_assoc($result)){
sql = "SELECT * FROM `booking` WHERE Book_First_Name = 'aa test' and laste <> '".$Today_ymd."' ";
$result = mysql_query($sql,$connection) or die("Couldn't execute $sql query. <br> mysql error: ".mysql_error());
//include('load-booking-variables.php');
include('convert-dates-to-mdy.php');
include('add-agency-logos.php');

$body = file_get_contents($Email_Alamo_Refund);
include('replace-email-variables.php');

// Send email to Client
$Subject = "Notice Regarding Your Hawaii Car Rental";
$emailaddress = $Email;
echo $row['Email']; echo "<br>";

[kuau]

OK, I ran the script for real and it added the send date to 159 records in the table (I added a test record), but it wrote only 83 lines to the text file. And there were 2 records with my email address, so I should have received 2 emails but received only one. Now I am worried that not all the people received the notice. I need to be able to trust the script or it isn't much use. Could you please tell me what is wrong, or could be improved in my code? I really need this script to work reliably as I'll need to use it over and over with different SELECT clauses. Thanks very much. :)


include('cr-connectdb.php');
require('class.phpmailer.php');

$Today_ymd = date("Y-m-d"); // 2010-08-31

// Retrieve Records from booking table
$sql = "SELECT `Book_Count` FROM `booking` WHERE `Book_Date` < '2010-6-30' AND `Book_Begin_Date` > '2010-8-31' AND (`Book_Comp` = 'Alamo' AND `country` NOT IN('CA', 'CW')) AND `Book_Pick` NOT LIKE 'Oahu%' AND `Book_Pick` NOT LIKE 'Maui - Kaan%' AND `Book_Confirm` <> 'Not Confirmed' ORDER BY `Book_Last_Name` ";
$result1 = mysql_query($sql,$connection) or die("Couldn't execute $sql query. <br> mysql error: ".mysql_error());

if(mysql_num_rows($result1) == 0){ echo "No rows returned\n"; exit; }

while($clientList = mysql_fetch_assoc($result1)){
$sql = "SELECT * FROM `booking` WHERE Book_Count = '".$clientList['Book_Count']."'";
$result = mysql_query($sql,$connection) or die("Couldn't execute $sql query. <br> mySQL Error: ".mysql_error());
while($row = @mysql_fetch_array($result)){
include('load-booking-variables.php');
include('convert-dates-to-mdy.php');
include('add-agency-logos.php');

$body = file_get_contents($Email_Alamo_Refund);
include('replace-email-variables.php');

// Send email to Client
$Subject = "Notice Regarding Your Hawaii Car Rental";
$emailaddress = $Email;
echo $emailaddress; echo "<br>";
include('send-client-email.php');

// Report Entry
$report = "Alamo Refund Info sent to: ".$name." at ".$Email." on ".$Today_mdy." \r\n";
$file = "/absolutepath/alamo-refund.txt";
if (!$file_handle = fopen($file,"a")) { echo "Cannot open $file file"; }
if (!fwrite($file_handle, $report)) { echo "Cannot write to $file file"; }
fclose($file_handle);

// Set Last Email Date to current date
$sql = "UPDATE booking SET `laste` = '".$Today_ymd."' WHERE `Book_Count` = '".$Book_Count."'" ;
$result2 = mysql_query($sql,$connection) or die("Couldn't execute $sql query. <br> mysql error: ".mysql_error());
}
}

[djr33]

The first place to start is that unless it works flawlessly, you should never use error suppression. Remove the @ and fix the related problems. Once you work that out everything may become clearer.

From what I can tell something in your loop is wrong (not inside the loop, but in the loop operation itself), so you will need to make sure everything there is correct-- then it will loop the right number of times. Additionally, do you see any pattern about the 83 entries? There must be some reason they are separated. Alternatively, it might be overloading your server. This is unlikely but it's easy to test: just add a sleep(1) somewhere and see if it more consistently sends the emails (that is-- test by writing to a file, not live yet). Note that even 1 second will add up here, so disable any limits (usually 30 seconds) on your host or you'll have an unrelated problem.