homepage Welcome to WebmasterWorld Guest from 54.197.183.230
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
PHP Mail Unique Messages to Different Users in Same Script
MySQL database contains records for several users with a time based email
thatsbadass



 
Msg#: 4578857 posted 5:05 am on May 29, 2013 (gmt 0)

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 <email@domain.com>";
$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

WebmasterWorld Senior Member swa66 us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4578857 posted 7:32 am on May 29, 2013 (gmt 0)

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



 
Msg#: 4578857 posted 8:01 pm on May 29, 2013 (gmt 0)

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

WebmasterWorld Senior Member swa66 us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4578857 posted 10:03 pm on May 29, 2013 (gmt 0)

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



 
Msg#: 4578857 posted 10:44 pm on May 29, 2013 (gmt 0)

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

WebmasterWorld Senior Member swa66 us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4578857 posted 12:30 pm on May 30, 2013 (gmt 0)

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

thatsbadass



 
Msg#: 4578857 posted 8:19 pm on Jun 5, 2013 (gmt 0)

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 <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>");
}
?>
========================================================

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved