bschultz Posted April 5, 2010 Share Posted April 5, 2010 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! Quote Link to comment https://forums.phpfreaks.com/topic/197676-php-email-from-database/ Share on other sites More sharing options...
ignace Posted April 5, 2010 Share Posted April 5, 2010 $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'; Quote Link to comment https://forums.phpfreaks.com/topic/197676-php-email-from-database/#findComment-1037412 Share on other sites More sharing options...
bschultz Posted April 5, 2010 Author Share Posted April 5, 2010 ignace...thank you VERY much! Worked perfectly. Now, I'll start to work on the "no matches" part. Brian Quote Link to comment https://forums.phpfreaks.com/topic/197676-php-email-from-database/#findComment-1037420 Share on other sites More sharing options...
bschultz Posted April 5, 2010 Author Share Posted April 5, 2010 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'; Quote Link to comment https://forums.phpfreaks.com/topic/197676-php-email-from-database/#findComment-1037428 Share on other sites More sharing options...
Pikachu2000 Posted April 5, 2010 Share Posted April 5, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/197676-php-email-from-database/#findComment-1037444 Share on other sites More sharing options...
bschultz Posted April 5, 2010 Author Share Posted April 5, 2010 $nomatchbody prints blank. UGH! Quote Link to comment https://forums.phpfreaks.com/topic/197676-php-email-from-database/#findComment-1037457 Share on other sites More sharing options...
Pikachu2000 Posted April 5, 2010 Share Posted April 5, 2010 Just noticed this: if (!matches) should be if( !$matches ), or better yet, if( $matches == 0 ) I would think. Quote Link to comment https://forums.phpfreaks.com/topic/197676-php-email-from-database/#findComment-1037464 Share on other sites More sharing options...
bschultz Posted April 5, 2010 Author Share Posted April 5, 2010 Man do I feel stupid! Thank you! Quote Link to comment https://forums.phpfreaks.com/topic/197676-php-email-from-database/#findComment-1037467 Share on other sites More sharing options...
bschultz Posted April 6, 2010 Author Share Posted April 6, 2010 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'; ?> Quote Link to comment https://forums.phpfreaks.com/topic/197676-php-email-from-database/#findComment-1037539 Share on other sites More sharing options...
bschultz Posted April 6, 2010 Author Share Posted April 6, 2010 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'; ?> Quote Link to comment https://forums.phpfreaks.com/topic/197676-php-email-from-database/#findComment-1037576 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.