Using the Right Loop with PHPMailer

[tektipsismyfavorite]

I'm using PHPMailer to send out emails based on what comes up in the Query. Using a do { } while ( ); seems to only send out the first record.

I'm using:

mysql_select_db($database, $connection);
$query = "SELECT * FROM events WHERE date BETWEEN '2006-06-26 00:00:00' AND '2006-07-26 23:59:59'";
$sql = mysql_query($query, $connection) or die(mysql_error());
$row_query = mysql_fetch_assoc($sql);

Currently I'm using something to the effect of:

do {
   $mail = new PHPMailer();
   $mail->From = "me@mydomain.com";
   $mail->AddAddress($row_query['email']);
   $mail->Subject = "Message";
   $mail->Body = $row_query['content'];
   if(!$mail->Send()) {
      $sendError = $mail->ErrorInfo;
   }
} while ($row_query = mysql_fetch_assoc($sql));

I also tried a for loop, but that didn't work at all. Basically I just want to loop through each row in the query result and send a PHPMailer.

 

[sleipnir214]

You should be using a while() loop:

 while ($row_query = mysql_fetch_assoc($sql));
{
	$mail = new PHPMailer();
	$mail->From = "me@mydomain.com";
	$mail->AddAddress($row_query['email']);
	$mail->Subject = "Message";
	$mail->Body = $row_query['content'];
	if(!$mail->Send())
	{
		$sendError = $mail->ErrorInfo;
	}
}

This eliminates the necessity to perform one fetch from the database before entering the loop.

If the loop only runs once in your version and does not run at all in mine, I would verify that your query is actually fetching data from the database. I am dubious that your query works because you're using "date" as a column name when "date" is a MySQL reserved word.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[jpadie]

for the date part of your query why not just use the date function in mysql?

$query = "SELECT * FROM events WHERE DATE(`date`) = '2006-06-26'";

 

[tektipsismyfavorite]

It's not actually my column name, but just for simplicity purposes i used that. I'm sure that it's pulling records because if i comment that mailer out, and output the loop into HTML, i see all the records i'm supposed to.

So, do I need to get rid of the first one?
($row_query = mysql_fetch_assoc($sql)

 

[tektipsismyfavorite]

I've tried doing while loops in the past, it just doesn't seem to be working. I'm new to PHP5, is there something pertaining to anything i'm doing different than 4?

 

[jpadie]

yes. get rid of the first call and replace the entire do...while loop with sleipnir214's code.

there is no difference in the user experience of loops between php4 and 5. there may well be differences in the underlying engine but i have never noticed it making any difference to my interpreted code.

i have found, with phpMailer on Windows, that i need to specify that phpmailer should use SMTP and provide a pointer to the smtp server i use. if you do not specify a handler phpmailer defaults to the mail() function and for some reason this throws errors in my installation (but not if i call mail() directly).

$mail->Host     = "localhost";  //or whatever
$mail->IsSMTP();

 

[tektipsismyfavorite]

well, the problem I'm having is it only goes through the mail script once, just for the first record.

here's my code:

//set variables for this minute
$todayDay = date("d");
$todayMonth = date("m");
$todayYear = date("Y");
$todayHour = date("H");
$todayMinute = date("i");

$alertStart = $todayYear."-".$todayMonth."-".$todayDay." ".$todayHour.":".$todayMinute.":00";
$alertEnd = $todayYear."-".$todayMonth."-".$todayDay." ".$todayHour.":".$todayMinute.":59";

mysql_select_db($database_ra_connect, $ra_connect);

$query_alerts = "SELECT *
FROM alerts, reminders, ra_users, providers
WHERE alerts.userid = ra_users.userid
AND alerts.active = '1'
AND alerts.reminderid = reminders.reminderid
AND ra_users.providerid = providers.providerid
AND alerts.alertFull BETWEEN '".$alertStart."' AND '".$alertEnd."'";

$alerts = mysql_query($query_alerts, $ra_connect) or die(mysql_error());
$totalRows_alerts = mysql_num_rows($alerts);

if($totalRows_alerts >= 1){
	//loop through the mail script for all alerts
	while ($row_alerts = mysql_fetch_assoc($alerts)){
		$mail = new PHPMailer();
	
		$mail->IsSMTP();							// set mailer to use SMTP
		$mail->Host = "mail.blah.com";	// specify main mail server
		$mail->SMTPAuth = true;						// turn on SMTP authentication
		$mail->Username = "myusername";				// SMTP username
		$mail->Password = "my***password";			// SMTP password
		
		$mail->From = "alerts@remindalert.com";
		$mail->FromName = "RA Mailer";
		$myMessage = $row_alerts['title'];
		
		if($row_alerts['txt'] == "1"){
			$mobile = $row_alerts['mobile'];
			$sendto1 = "\"".$mobile."@".$row_alerts['attach']."\"";
			$mail->AddAddress($sendto1);
		}
		if($row_alerts['email'] == "1"){
			$sendto2 = "\"".$row_alerts['emailAddress']."\", \"".$row_alerts['firstName']." ".$row_alerts['lastName']."\"";
			$mail->AddAddress($sendto2);
		}
	
		$mail->AddReplyTo("info@remindalert.com", "RemindAlert");
		$mail->IsHTML(false);                                  // set email format to non-HTML
		
		$mail->Subject = "RemindAlert";
		$mail->Body    = $myMessage;
//		$mail->AltBody = ;
		
		if(!$mail->Send()) {
			//email me the problem
			$sendError = $mail->ErrorInfo;
			$mail2 = new PHPMailer();
			
			$mail2->IsSMTP();							// set mailer to use SMTP
			$mail2->Host = "mail.blah.com";	// specify main mail server
			$mail2->SMTPAuth = true;						// turn on SMTP authentication
			$mail2->Username = "myusername";				// SMTP username
			$mail2->Password = "my****password";			// SMTP password
			
			$mail2->From = "errors@remindalert.com";
			$mail2->FromName = "RA Mailer";
			$mail2->AddAddress("support@remindalert.com");
			
			$mail2->IsHTML(true);                                  // set email format to HTML
			
			$mail2->Subject = "RemindAlert Error";
			$mail2->Body    = "RemindAlert Failure: <br><br>".$sendError;
			$mail2->Send();
			$mail2->ClearAddresses();
		} else {
			$mail->ClearAddresses();
		} 
	}  //end loop statement
}  //end if statement

 

[sleipnir214]

First, this line:

$totalRows_alerts = mysql_num_rows($alerts);

and the if-statement surrounding your while-loop are unnecessary. If there are no records available through the result handle, the while-loop will not run.


Second, I recommend you use single-quotes more. For example, the line:

$sendto1 = "\"" . $mobile . "@" . $row_alerts['attach'] . "\"";

can be written without the escaped internal doublequotes as:

$sendto1 = '"' . $mobile . "@" . $row_alerts['attach'] . '"';

which I think are more readable without all the backslashes.



With all that out of the way, the most likely reason the code is sending one email is that there is only one record in the result of the query. What have you done to verify there is more than one record in the result set?





Want the best answers? Ask the best questions! TANSTAAFL!

 

[tektipsismyfavorite]

i re-ran the query and outputted the result using the exact same loop later down the page.

//i leave the value of the query set to what it was before.
mysql_select_db($database_ra_connect, $ra_connect);
$alerts = mysql_query($query_alerts, $ra_connect) or die(mysql_error());
$totalRows_alerts = mysql_num_rows($alerts);

while ($row_alerts = mysql_fetch_assoc($alerts)){
    //this is just one of the fields
    echo $row_alerts['alertid']."<br>";
}

If you would like to see the displayed results (and the query):

http://www.remindalert.com/test.php

The way I know my mail script is only running once, is because it's supposed to send a message to me @ the email address (not the sample one shown), and it only sends 1 message, not 3.

 

[jpadie]

this bit may be failing

          $sendto2 = "\"".$row_alerts['emailAddress']."\", \"".$row_alerts['firstName']." ".$row_alerts['lastName']."\"";
            $mail->AddAddress($sendto2);

from memory the usual syntax is $mail->AddAddress(emailaddress, name);

and you are trying to pile the whole thing into the email address variable.

 

[sleipnir214]

I don't think your code works the way you think it does.

If the message you are supposed to get will be sent by code inside the if-statement block which begins:

if(!$mail->Send()) {

Then you're only going to get an email for a problems. If there are no problems, you're not getting any errors, then you're not going to get any emails.

I strongly recommend that you footprint your code with some print statements to verify the number of times the script is running the while-loop.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[sleipnir214]

I think you're also going to have problems with this piece of your code:

if($row_alerts['email'] == "1")
{
    $sendto2 = "\"" . $row_alerts['emailAddress'] . "\", \"" . $row_alerts['firstName'] . " " . $row_alerts['lastName'] . "\"";
    $mail->AddAddress($sendto2);
}

the AddAddress() method takes either a single parameter of an simple email address or two separate parameters, one consisting of the email address and another consisting of the recipient name. You seem to be trying to do a hybrid of the two by concatenating the two together and using it in the simple email address parameter position.

I think the code would be more correctly:

if($row_alerts['email'] == "1")
{
    $mail->AddAddress($row_alerts['emailAddress'], $row_alerts['firstName'] . " " . $row_alerts['lastName']);
}

Want the best answers? Ask the best questions! TANSTAAFL!

 

[sleipnir214]

Here is your code back with my changes:

<?php
$alertStart = date ('Y-m-d H:i:00');
$alertStart = date ('Y-m-d H:i:59');

mysql_select_db($database_ra_connect, $ra_connect);

$query_alerts =
"SELECT *
FROM alerts, reminders, ra_users, providers
WHERE alerts.userid = ra_users.userid
AND alerts.active = '1'
AND alerts.reminderid = reminders.reminderid
AND ra_users.providerid = providers.providerid
AND alerts.alertFull BETWEEN '" . $alertStart . "' AND '" . $alertEnd . "'";

$alerts = mysql_query($query_alerts, $ra_connect) or die(mysql_error());

while ($row_alerts = mysql_fetch_assoc($alerts))
{
	$mail = new PHPMailer();

	$mail->IsSMTP();                            // set mailer to use SMTP
	$mail->Host = 'mail.blah.com';    // specify main mail server
	$mail->SMTPAuth = true;                        // turn on SMTP authentication
	$mail->Username = 'myusername';                // SMTP username
	$mail->Password = 'my***password';            // SMTP password
	
	$mail->From = 'alerts@remindalert.com';
	$mail->FromName = 'RA Mailer';
	$myMessage = $row_alerts['title'];
    
	if($row_alerts['txt'] == '1')
	{
		$mobile = $row_alerts['mobile'];
		$sendto1 = $mobile . '@' . $row_alerts['attach'];
		$mail->AddAddress($sendto1);
	}
	if($row_alerts['email'] == '1')
	{
		$mail->AddAddress($row_alerts['emailAddress'], $row_alerts['firstName'] . ' ' . $row_alerts['lastName']);
	}
	
	$mail->AddReplyTo('info@remindalert.com', 'RemindAlert');
	$mail->IsHTML(false);                                  // set email format to non-HTML
	
	$mail->Subject = 'RemindAlert';
	$mail->Body    = $myMessage;
	//        $mail->AltBody = ;
	
	if(!$mail->Send())
	{
		//email me the problem
		$sendError = $mail->ErrorInfo;
		$mail2 = new PHPMailer();
		
		$mail2->IsSMTP();                            // set mailer to use SMTP
		$mail2->Host = 'mail.blah.com';    // specify main mail server
		$mail2->SMTPAuth = true;                        // turn on SMTP authentication
		$mail2->Username = 'myusername';                // SMTP username
		$mail2->Password = 'my****password';            // SMTP password
		
		$mail2->From = 'errors@remindalert.com';
		$mail2->FromName = 'RA Mailer';
		$mail2->AddAddress('support@remindalert.com');
		
		$mail2->IsHTML(true);                                  // set email format to HTML
		
		$mail2->Subject = 'RemindAlert Error';
		$mail2->Body    = 'RemindAlert Failure: <br><br>' . $sendError;
		$mail2->Send();
	}
}  //end loop statement
?>

I also took out your roundabout way of creating the date strings at the beginning of your script snippet.



Want the best answers? Ask the best questions! TANSTAAFL!