PHP Email from Database

[bschultz]

I would like to send an email with the results from a database query.  What I have written works, but it sends a separate email for each match.  I would like ALL matches in ONE email.

 

Here's the code

 

$sql = "SELECT * FROM calendar WHERE WEEK(date) = WEEK(DATE_ADD(NOW(), INTERVAL 1 DAY)) AND home = 'schoolnamehere'"; 
$dbq = mysql_query($sql,$dbc); 

while ($row = mysql_fetch_array($dbq)) {

mail($to,$subject,"".$row[date]." \n ".$row[time]." \n ".$row[sport]." - ".$row[visitor]." at ".$row[home]."   ".$row[ump1] ."   ".$row[ump2] ."   ".$row[ump3] ."   ".$row[ump4] ."   ".$row[ump5]." \n 
".$row[field]."  \n ".$row[notes]." \n=====================================================" ,$headers);

echo "Mail Sent.";

}

 

Now, I know why it's doing what it's doing.  The call to MAIL is INSIDE the while loop.  The problem is, I can't figure out where to put the call to MAIL and where to put the query, and still have it work.

 

Can someone help point me in the right direction please?

 

Thanks!

[ignace]

$body = '';
$dash = str_repeat('=', 70);
while ($row = mysql_fetch_assoc($result)) {
    $message = $row['date'] . PHP_EOL .
               $row['time'] . PHP_EOL .
               $row['sport'] . ' - ' . $row['visitor'] . ' at ' . $row['home'] . '   ' .
               $row['ump1'] . '   ' . $row['ump2'] . '   ' . $row['ump3'] . '   ' . $row['ump4'] . '   ' . $row['ump5'] . PHP_EOL .
               $row['field'] . PHP_EOL .
               $row['notes'] . PHP_EOL .
               $dash . PHP_EOL;
     $body   = $body . $message . PHP_EOL;
}

$status = mail($to, $subject, $body, $headers);
echo 'Mail', (!$status ? ' not ' : ''), 'sent';

[bschultz]

ignace...thank you VERY much!  Worked perfectly.  Now, I'll start to work on the "no matches" part.

 

Brian

[bschultz]

Alright, upon looking at the layout of this script, it sends the email regardless of whether or not there are matches. 

 

I tried this...but it sent an email with a blank body.

 

$sql = "SELECT * FROM calendar WHERE WEEK(date) = WEEK(DATE_ADD(NOW(), INTERVAL 1 DAY)) AND home = 'LOW'"; 
$dbq = mysql_query($sql,$dbc); 
$matches = 0; 
$body = '';
$nomatchbody = '';
$dash = str_repeat('#', 70);
while ($row = mysql_fetch_assoc($dbq)) {
$matches++; 
               $message = $row['date'] . PHP_EOL .
               $row['time'] . PHP_EOL .
               $row['sport'] . ' - ' . $row['visitor'] . ' at ' . $row['home'] . '   ' .
               $row['ump1'] . '   ' . $row['ump2'] . '   ' . $row['ump3'] . '   ' . $row['ump4'] . '   ' . $row['ump5'] . PHP_EOL .
               $row['field'] . PHP_EOL .
               $row['notes'] . PHP_EOL .
               $dash . PHP_EOL;

if (!matches) {
$nomatchbody = "Brian\n\nThe BUA is not scheduled to work any games in your town next week \n\n If this does not match up with your schedule, please let me know ASAP.  As always, thanks for your interest in the BUA.";
}
     $body   = $body . $message . $nomatchbody . PHP_EOL;
}	 
$status = mail($to, $subject, $body, $headers);
echo 'Mail', (!$status ? ' not ' : ''), 'sent';

[Pikachu2000]

Temporarily comment out the mail() function, and echo the $body variable instead so you can see if $body holds the data you'd expect it to hold.

[bschultz]

$nomatchbody prints blank.

 

UGH!

[Pikachu2000]

Just noticed this: if (!matches) should be if( !$matches ), or better yet, if( $matches == 0 ) I would think.

[bschultz]

Man do I feel stupid!  Thank you!

[bschultz]

Alright...in trying to "clean up" a few things...I managed to break this again.  It's now back to sending just one record (the first) and not ALL of the records!

 

<?php 
/////////////////////////////////////////////  Email From a Database a List of Events For The Week  /////////////////////////////////////////////

//create a set of dates...for the coming week...
        $lowEnd=date("w");
        $lowEnd=-$lowEnd;
        $highEnd=$lowEnd + 6;
        $weekday=0;
        for ($i=$lowEnd; $i<=$highEnd; $i++) {
            $WeekDate[$weekday]=date("m/d",mktime(0, 0, 0, date("m")  , date("d")+$i, date("Y")));
            $weekday++;
}

$body = '';
$intro = "Troy and Shelia \n\n Here are the games on the BUA schedule for the week of ( $WeekDate[0] through $WeekDate[6] ): \n\n ";
$close = "If this does not match up with your schedule, please let me know ASAP. \n\n As always, thanks for your interest in the BUA! \n\n name \n address \n phone";
$dash = str_repeat('=', 70);


///// start the email constants /////////////////////////////////////////////////////
$to  = 'first recipient <recipient1@domain.com>' . ', '; // note the comma...we're using more than one "To" address ////////////////////
$to .= 'second recipient <recipient1@domain.com>';

$subject = "Baseball and Softball Umpires For Next Week";

$headers = 'From: my name <email@domain.com>' . "\r\n" .
    'Reply-To: email@domain.com' . "\r\n" .
    'X-Mailer: PHP/' . phpversion();


$dbc = mysql_connect('xxx','xxx','xxx');  
mysql_select_db('xxx_umps',$dbc);  
//now get stuff from a table  
$sql = "SELECT * FROM calendar WHERE WEEK(date) = WEEK(DATE_ADD(NOW(), INTERVAL 1 DAY)) AND home = 'Bemidji'";           
$rs = mysql_query($sql,$dbc);  
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {
$matches++; 

             $message = $row['date'] . PHP_EOL .
             $row['time'] . PHP_EOL .
             $row['sport'] . ' - ' . $row['visitor'] . ' at ' . $row['home'] . '   ' .
             $row['ump1'] . '   ' . $row['ump2'] . '   ' . $row['ump3'] . '   ' . $row['ump4'] . '   ' . $row['ump5'] . PHP_EOL .
             $row['field'] . PHP_EOL .
             $row['notes'] . PHP_EOL .
             $dash . PHP_EOL;
}

if (!$matches) {
$nomatchbody = "The BUA is NOT scheduled to work any games in your town next week. \n\n ";
}

$body   = $body . $intro . $message . $nomatchbody . $close . PHP_EOL;	
   
$status = mail($to, $subject, $body, $headers);
echo 'Mail', (!$status ? ' not ' : ''), 'sent';
?>

[bschultz]

I got it...

 

<?php 
/////////////////////////////////////////////  Email From a Database a List of Events For The Week  /////////////////////////////////////////////

//create a set of dates...for the coming week...
        $lowEnd=date("w");
        $lowEnd=-$lowEnd;
        $highEnd=$lowEnd + 6;
        $weekday=0;
        for ($i=$lowEnd; $i<=$highEnd; $i++) {
            $WeekDate[$weekday]=date("m/d",mktime(0, 0, 0, date("m")  , date("d")+$i, date("Y")));
            $weekday++;
}

$body = '';
$intro = "Troy and Shelia \n\n Here are the games on the BUA schedule for the week of ( $WeekDate[0] through $WeekDate[6] ): \n\n ";
$close = "If this does not match up with your schedule, please let me know ASAP. \n\n As always, thanks for your interest in the BUA! \n\n name\n address \n phone";
$nomatches = "";
$dash = str_repeat('=', 70);


///// start the email constants /////////////////////////////////////////////////////
$to  = 'recipient name <recipient1@domain.com>' . ', '; // note the comma...we're using more than one "To" address ////////////////////
$to .= 'recipient name2 <recipient2@domain.com>';

$subject = "Baseball and Softball Umpires For Next Week";

$headers = 'From: my name<myemail@domain.com>' . "\r\n" .
    'Reply-To: myemail@domain.com' . "\r\n" .
    'X-Mailer: PHP/' . phpversion();


$dbc = mysql_connect('xxx','xxx','xxx');
mysql_select_db('database_name',$dbc);  
//now get stuff from a table  
$sql = "SELECT * FROM table WHERE WEEK(date) = WEEK(DATE_ADD(NOW(), INTERVAL 1 DAY)) AND home = 'LOW'";           
$rs = mysql_query($sql,$dbc);  
$matches = 0; 
$body = '';
$dash = str_repeat('=', 70);
while ($row = mysql_fetch_assoc($rs)) {
$matches++;
    $message = $row['date'] . PHP_EOL .
               $row['time'] . PHP_EOL .
               $row['sport'] . ' - ' . $row['visitor'] . ' at ' . $row['home'] . '   ' .
               $row['ump1'] . '   ' . $row['ump2'] . '   ' . $row['ump3'] . '   ' . $row['ump4'] . '   ' . $row['ump5'] . PHP_EOL .
               $row['field'] . PHP_EOL .
               $row['notes'] . PHP_EOL .
               $dash . PHP_EOL;
     $body   = $body . $message . PHP_EOL;

}
if (!$matches){
$nomatches = "According to our schedule, the BUA DOES NOT have any games during this week. \n\n ";
}

$status = mail($to, $subject, $intro . $body . $nomatches .  $close, $headers);
echo 'Mail', (!$status ? ' not ' : ''), 'sent';
?>