I am trying to loop through each record in my database and send an email to the email address associated with that records Once the email is sent I change a value from email_sent=0 to email_sent=1 so it doesnt get sent more than once.

I am expecting to get record 1 emailed to email 1, record 2 emailed to email 2, and so on until all records are marked complete.

I tried placing the loop in 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 appended record 2, the third email had record 1, appended record 2 and record 3. Each record was marked as email_sent=1.

I also tried just running the query three times within the same page with some changes to try to close the loop but it didnt work either.

The code below is working to send record 1 to email 1, marks the record complete. When run again it sends record 2 to email 2 and marks it complete. I then have to run it once for each record in the database.

I am using LIMIT 1 because if it isn't there I got all records sent in one email.

Here is the code. Any help or thoughts would be greatly 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 <email@something.com>"; 
$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>"); 
} 
 ?>

    Your while() loop:

    while ($row = mysql_fetch_assoc($result)) 
{ 
$from = "Some Name <email@something.com>"; 
$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"; 
}

    is doing nothing but overwriting strings stored in variables for each record in your database.

    Then, outside the loop, you execute the following code once for the last record in the result set:

    { 
$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>"); 
}

    EDIT: Also, when posting PHP code, please use the board's [noparse]

    ..

    [/noparse] bbcode tags as they make your code much easier to read and analyze.

      First, I would only update the database record if the mail() function returned true. But with that being said, using mail() like this is really not its intended use, and it's usually discouraged from using it in a loop. You can check out alternatives by reading the manual on the [man]mail/man function.

      The Manual wrote:

      It is worth noting that the mail() function is not suitable for larger volumes of email in a loop. This function opens and closes an SMTP socket for each email, which is not very efficient.

      For the sending of large amounts of email, see the » PEAR::Mail, and » PEAR::Mail_Queue packages.

      Also: obligatory stop using the mysql extension and use mysqli* or PDO instead.

        @, thanks for the pointer on the

        
I am only expecting to send a dozen or so emails per day, if that.

That being the case should I still look for alternatives? I like the simplicity of using mail() and it is working. I just would rather not have to schedule my CRON to run 5 times to get 5 emails out.
          Write a Reply...