sending mails to different users with different values from database

[I-AM-OBODO]

Hi all.

 

How can i send mail to multiple users at the same time with each user getting their own related data.

I'm using php mailer as an engine to send the mail.

Based on this, i'd have loved to setup a cron for it but i do not know how, so i figure i'd just do it manually before i get to know how to setup a cron job.

 

It's just to send a reminder to users and each user has a different subscription expiry time. I want each user to get their respective expiration date, expiry day etc.

 

Thanks

 

 

 
 if(isset($_POST['send_reminder'])){
    
$sql = "SELECT * FROM users WHERE status = '$status'";
$stmt = $pdo->query($sql);
$stmt->execute();

while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
$name = $row['name'];
$acct_no = $row['acct_no'];
$email_addresses = $row['email'];  
$expiry_date = $row['expiry_date'];
$expiry_day = $row['expiry_day'];

}
 
$message="Hello $name,<br>
<p>
This is to remind you that your subscription will expire in $expiry_day.
</p>
<p>
Details as follows:
Name: $name<br>
Account Number: $acct_no<br>
Email: $email_addresses<br>
Expire in days: $expiry_day<br>
Expiry Date: $expiry_date
</p>
<p>
Thank you
</p>
 
$mail = new PHPMailer;

//$mail->SMTPDebug = 3;                               // Enable verbose debug output

$mail->isSMTP();                                      // Set mailer to use SMTP
$mail->Host = 'mail.server.com';  // Specify main and backup SMTP servers
$mail->SMTPAuth = true;                               // Enable SMTP authentication
$mail->Username = 'mails@services.cap';                 // SMTP username
$mail->Password = 'password';                           // SMTP password
$mail->SMTPSecure = 'ssl';                            // Enable TLS encryption, `ssl` also accepted
$mail->Port = 465;                                    // TCP port to connect to

$mail->From = mails@services.cap';
$mail->FromName = 'Club 404';
$mail->addAddress($email_addresses);     // Add a recipient


$mail->WordWrap = 587;                                 // Set word wrap to 50 characters

$mail->AddEmbeddedImage("../img/logo.png", "my_logo");
$mail->isHTML(true);                                  // Set email format to HTML

$mail->Subject = 'REMINDER ON CLUB EXPIRY DATE';
$mail->Body    = $mess;
$mail->send();
 
}
 

[Jacques1]

Really all you have to do is put the mail sending logic into the loop rather than after it. Right now, you just keep overwriting the same set of variables in your loop, and then you send a single mail with the data that happens to come last.

 

However, you still need to fix some things:

• You should send the e-mails in a controlled manner with an upper limit per script execution (e. g. at most 100 mails). Clearing your entire database table at once is not a good idea, because it may overload your server or even be abused for DoS attacks.
• Never insert raw strings into an HTML context. This applies to both websites and e-mails. While most e-mail clients won't execute any injected scripts, this is still very unprofessional and may be flagged as malicious content. Use HTML-escaping with htmlspecialchars().

[mac_gyver]

your loop should only be retrieving the data, forming the $message body with the data values, clearing any previous To: address (see the ClearAddresses() method), set the To: address from the data, and call the send() method. all the rest of the phpmailer related logic should exist only once and come before the start of your loop.

 

you would also want to check if the send() method indicated success or failure. if it failed, log the error information and the user's email address. if it was successful, you would probably want to set a status value somewhere to indicate that the email was sent (though this is no guarantee that it was received) so that you don't repeatedly send the same reminder email.

[I-AM-OBODO]

Ok. Thanks guys.

i've modified the code.

But @mac_gyver I'm not sure how to use the ClearAddresses() method. Hope what i did is right.

@jacques1, i know sending all the mails at once will curse flooding, was thinking i'd find a way around that later but thanks for reminding. but how will the script know the first hundred mails it has sent? or does it mean i will have to create another table so that after sending a 100 it marks them as sent/1 as the case maybe it updates the table column and loop again for those not sent. another problem i envisage is the time when the script needs to run again cos there's limit it can run for x hour.

 

Thanks

 

my modified code

 
 if(isset($_POST['send_reminder'])){
    
$sql = "SELECT * FROM users WHERE status = '$status' LIMIT 100";
$stmt = $pdo->query($sql);
$stmt->execute();

$message="Hello $name,<br>
<p>
This is to remind you that your subscription will expire in $expiry_day.
</p>
<p>
Details as follows:
Name: $name<br>
Account Number: $acct_no<br>
Email: $email_addresses<br>
Expire in days: $expiry_day<br>
Expiry Date: $expiry_date
</p>
<p>
Thank you
</p>
";

$mail = new PHPMailer;

//$mail->SMTPDebug = 3;                               // Enable verbose debug output

$mail->isSMTP();                                      // Set mailer to use SMTP
$mail->Host = 'mail.server.com';  // Specify main and backup SMTP servers
$mail->SMTPAuth = true;                               // Enable SMTP authentication
$mail->Username = 'mails@services.cap';                 // SMTP username
$mail->Password = 'password';                           // SMTP password
$mail->SMTPSecure = 'ssl';                            // Enable TLS encryption, `ssl` also accepted
$mail->Port = 465;                                    // TCP port to connect to

$mail->From = 'mails@services.cap';
$mail->FromName = 'Club 404';



$mail->WordWrap = 587;                                 // Set word wrap to 50 characters

$mail->AddEmbeddedImage('../img/logo.png', 'my_logo');
$mail->isHTML(true);                                  // Set email format to HTML

$mail->Subject = 'REMINDER ON CLUB EXPIRY DATE';

while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
$name = $row['name'];
$acct_no = $row['acct_no'];
$email_addresses = $row['email'];  
$expiry_date = $row['expiry_date'];
$expiry_day = $row['expiry_day'];


//$mail->addAddress($email_addresses);     // Add a recipient
$mail->ClearAllRecipients($email_addresses);// not too sure how to use it
$mail->Body = $mess;

if(!$mail->send()) {
    echo 'Problem sending message.';
} else {
    echo 'Message has been sent successfully';
}
}

 }

Edited September 29, 2015 by Mr-Chidi

[I-AM-OBODO]

your loop should only be retrieving the data, forming the $message body with the data values, clearing any previous To: address (see the ClearAddresses() method), set the To: address from the data, and call the send() method. all the rest of the phpmailer related logic should exist only once and come before the start of your loop.

 

you would also want to check if the send() method indicated success or failure. if it failed, log the error information and the user's email address. if it was successful, you would probably want to set a status value somewhere to indicate that the email was sent (though this is no guarantee that it was received) so that you don't repeatedly send the same reminder email.

 

Thanks. I have just modified the code. But I'm not sure if i got the ClearAddresses() method usage correctly.

[I-AM-OBODO]

 

Really all you have to do is put the mail sending logic into the loop rather than after it. Right now, you just keep overwriting the same set of variables in your loop, and then you send a single mail with the data that happens to come last.

 

However, you still need to fix some things:

• You should send the e-mails in a controlled manner with an upper limit per script execution (e. g. at most 100 mails). Clearing your entire database table at once is not a good idea, because it may overload your server or even be abused for DoS attacks.
• Never insert raw strings into an HTML context. This applies to both websites and e-mails. While most e-mail clients won't execute any injected scripts, this is still very unprofessional and may be flagged as malicious content. Use HTML-escaping with htmlspecialchars().

 

 

Thanks. I modified the code but

i know sending all the mails at once will curse flooding, was thinking i'd find a way around that later but thanks for reminding. but how will the script know the first hundred mails it has sent? or does it mean i will have to create another table so that after sending a 100 it marks them as sent/1 as the case maybe it updates the table column and loop again for those not sent.

[Jacques1]

I'd add two columns to the table: sent (a boolean) and sending_failures (an unsigned integer). Each time the script executes, you pick a fixed number of records which haven't been sent yet and had less than, say, 10 failed attempts. Order them ascending by the time they were entered into the table so that you get a queue. If the mail was sent successfully, you mark it as sent, otherwise you increment the number of failures.