PHP Mail Unique Messages to Different Users in Same Script

MySQL database contains records for several users with a time based email

[thatsbadass]

I am trying to email unique messages to individuals using information from a MySQL database. The emails are sent when a date is within 30 days.

Essentially the email recipient will get a user id, date, and some other values from the database that are specific to them when a date is reached.

I am having trouble figuring out how to send an email to recipient1 with their information, then an email to recipient2 with their information, recipient3 with their information, and so on, all within the same script.

I have gotten around this by setting a value within the database to look up whether or not an email was already sent and selecting LIMIT 1. If no email was sent the first record is selected and an email is sent. Upon completion the value is updated in the database so that record is skipped the next time.

I run this script using a CRON job on the server and it is working for one email at a time. It runs hourly, which I could tighten up but would prefer a single script to batch run for everyone once daily.

Does anyone know how I can accomplish sending separate emails to multiple recipients with recipient specific information from the database within a single script?

Here is my code:
===========================================================
<?php
require_once("dbconn.php");

if (!mysql_connect($DB_HOST, $DB_USER, $DB_PASSWORD))
die("Can't connect to database");
if (!mysql_select_db($DB_DATABASE))
die("Can't select database");
?>
<?php
$result = mysql_query("SELECT * FROM tablexyz WHERE field_target BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE() AND field_status <> 'Complete' AND email_sent = 0 LIMIT 1");
if (!result) {
die("No data");
}
while ($row = mysql_fetch_array($result))
{
$from = "Name <[e-mail]>";
$header = "From: ".$from."rn";
$header .= "Reply-To: ".$from."rn";
$to = $row['email_to'];
$subject .= "Value ".$row['field_id']." ".$row['field_type']." Due ".$row['field_target'];
$record_id = $row['record_id'];

$emailBody .= "Employee: ".$row['field_id']."
Field Type: ".$row['field_type']."
Start Date: ".$row['date_start']."
Target Field: ".$row['field_target']."
Email: ".$row['email_to']." \n";
}
{
$result = mysql_query("UPDATE tablexyz SET email_sent='1' , email_log_date=date('Y-m-d') WHERE record_id='$record_id'");
}
mail($to, $subject, $emailBody);

if (mail($emailto, $emailsubject, $emailBody)) {
echo("<p>Message successfully sent! Record '$record_id'.</p>");
} else {
echo("<p>Message delivery failed...</p>");
}
?>
===========================================================

[swa66]

Take a look here: [php.net...]

mail() needs a 4th parameter to do something with those headers.
For the rest, you're calling mail() twice, once without testing the return value, and once with testing, but with empty parameters as far as I can tell.

[thatsbadass]

Thank you. That will help as I work on the "from" issues.

I should have been more clear.

I am wanted to send more than one email in the script.

I think I want to loop through query/send several times to accomplish the custom email to each recipient.

Something like a loop through the query unless there are no more records needing to be sent to then end. Just not sure how I would code that out.

[swa66]

Reread your original question as well.

the '

LIMIT 1

' in your SQL query makes it only return one row maximum from your database.

Remove that and you should get all rows that match. Then just loop over your mysql results and send an email each time.

[thatsbadass]

Ok that is what I was thinking.

I had the LIMIT 1 on because I was getting multiple records in a single email if the recipient's email address was attached to more than one record.

I want one record per email so I had to limit it.

Thanks for the confirmation that the loop is the correct method for achieving this.

Any recommendations on how to write that? :)

I have done ASP loops but havent done a loop/if/else in PHP before.

[swa66]

You have a while loop in there, just send the email from within that loop.

[thatsbadass]

I'm sorry but I am having a bugger of a time getting it to loop correctly.

I tried a few different places in the code and ended up getting the correct number of emails. The first email had the correct record 1, the second email started with record 1 and added record 2, the third email had record 1, record 2 and record 3.

I even tried just running the query three times within the same page. As you can see in the code each time through I mark the record complete, email_sent=1. I ended up with the same three emails.

I am expecting to get email 1 with record 1, email 2 with record 2, etc, until all records are marked complete, changed from email_sent=0 to email_sent=1.

Here is the current code which works to send one email correctly each time it is run. Any help with the while loop placement would be very appreciated.
========================================================
<?php
require_once("dbconnxyz.php");

if (!mysql_connect($DB_HOST, $DB_USER, $DB_PASSWORD))
die("Can't connect to database");
if (!mysql_select_db($DB_DATABASE))
die("Can't select database");
?>
<?php
$result = mysql_query("SELECT * FROM tbl_test WHERE date_target BETWEEN CURDATE() AND ADDDATE(CURDATE(), INTERVAL 30 DAY) AND rev_status <> 'Complete' AND email_sent = 0 LIMIT 1");
if (!result) {
die("No data");
}
while ($row = mysql_fetch_assoc($result))
{
$from = "Some Name <[e-mail]>";
$completerecord = "http://xyz.com/some.php?record_id=";
$header = "From: ".$from."\r\n";
$header .= "Reply-To: ".$from;
$to = $row['email_to'];
$subject .= "Some text ".$row['xyz_id']." ".$row['record_type']." Due ".$row['date_target'];
$record_id = $row['record_id'];

$emailBody .= "You have some email text here in the body."."
"."
XYZ: ".$row['xyz_id']."
Record Type: ".$row['record_type']."
Start Date: ".$row['date_start']."
Target Date: ".$row['date_target']."
Complete: ".$completerecord.$row['record_id']." \n";
}
{
$result = mysql_query("UPDATE tbl_test SET email_sent='1' , email_log_date=date('Y-m-d') WHERE record_id='$record_id'");
}

if (mail($to, $subject, $emailBody, $header)) {
echo("<p>Message successfully sent! Record '$record_id'.</p>");
} else {
echo("<p>Message delivery failed...</p>");
}
?>
========================================================