mysql query result to email

I need to create an email where the message body pulled from mysql table (multi row). SIMPLE.
THE GOAL
message body:
Below is a list of new email addresses:

email@email.com
email2@email2.com
email3@email3.com

Thanks!

WHAT I CURRENTLY HAVE
$sql = "SELECT email , create_date FROM mailing_requests  WHERE ( create_date < NOW( ) AND create_date >= NOW( ) - INTERVAL 1  DAY ) AND market_id = 4 LIMIT 0, 30";

$result  = mysql_query($sql) ;

while ($row = mysql_fetch_row($result)) {

    foreach($row AS $col) {
        echo '\r\n\n' . $col . '\r\n\n';
    }


$email_from = "NoReply <NoReply@xxxxxx.com>"; // Who the email is from
$email_subject = "Yesturday's  Emails"; // The Subject of the email
$email = "xxxxx@xxxxxx.com"; // Who the email is too
$headers = "From: ".$email_from;

// mail($email, $email_subject, $email_message, $headers);

I am totally stuck!
kpandelakisAsked:
Who is Participating?
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:

>> I realized that I was missing a close curly bracket ....

That's one of the reasons that I detest the coding styles where brackets are placed on the end of the statement and program logic is indicated strictly by the indentation.  It's too easy to do exactly what happened here.  Especially with large programs.

I take some flak for it, particularly from younger programmers, but my code always looks like this:

while ($row = mysql_fetch_row($result))
{
  foreach($row AS $col)
  {
    $message .=  $col . '\r\nr\\n';
  }
}

Glad that you found it.  :)
Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi kpandelakis,

You're pretty close.  :)   The problem is that echo writes its output to the output pipe, and the mail function's 'message' parameter wants a string.

In the while loop, just concatenate the data into a variable, then pass that to mail as the $email_message parameter.



Good Luck,
Kent
0
 
kpandelakisAuthor Commented:
HI Kdo!
Please dont be overly impressed by my efforts. As I do not really understand your suggestion. TOTAL newby here.

Is this what you mean?

$sql = "SELECT email , create_date FROM mailing_requests  WHERE ( create_date < NOW( ) AND create_date >= NOW( ) - INTERVAL 1  DAY ) AND market_id = 4 LIMIT 0, 30";

$email_message  = "Below is a list of new email addresses:\r\n\n";
 
$result  = mysql_query($sql) ;

while ($row = mysql_fetch_row($result)) {

    foreach($row AS $col) {
        $email_message .=  '\r\n\n' . $col . '\r\n\n';
    }

$email_message  = "Thanks!\r\n\n";

$email_from = "NoReply <NoReply@xxxxxx.com>"; // Who the email is from
$email_subject = "Yesturday's  Emails"; // The Subject of the email
$email = "xxxxx@xxxxxx.com"; // Who the email is to
$headers = "From: ".$email_from;

// mail($email, $email_subject, $email_message, $headers);
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Kent OlsenData Warehouse Architect / DBACommented:
Hi kpandelakis,

Yep.  :)  


You should be able to test fire this one.....


0
 
ygouthamCommented:
one line i would change is a forgotten period "." in the code


while ($row = mysql_fetch_row($result)) {

    foreach($row AS $col) {
        $email_message .=  '\r\n\n' . $col . '\r\n\n';
    }

$email_message  .= "Thanks!\r\n\n";
//THE PERIOD IN THE ABOVE LINE IS MISSING. OTHERWISE THE ENTIRE MESSAGE GETS WIPED OUT!!!
0
 
kpandelakisAuthor Commented:
OK new problem. I am getting a parse error "Unexpected $ in line 30". This is line 30:
LIne 30 is the PHP END tag "?>".

Here is the WHOLE script:
<?php

/********************************************** ONLY touch below if you KNOW what you are doing! ********************************************/

$link = mysql_connect('host', 'the_table', 'XXXX');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_select_db("DB_name");

/********************************************** GET emails *******************************************/
$sql = "SELECT email , create_date FROM mailing_requests  WHERE ( create_date < NOW( ) AND create_date >= NOW( ) - INTERVAL 1  DAY ) AND market_id = 4 LIMIT 0, 30";
$message = 'Below is a list of new email addresses:\r\n\n';
$result  = mysql_query($sql) ;

while ($row = mysql_fetch_row($result)) {
 foreach($row AS $col) {
 $message .= '\r\n\n' . $col . '\r\n\n';
}

$message .= "Thanks!\r\n\n";

$email_from = "NoReply <NoReply@XXXX.com>"; // Who the email is from
$email_subject = "Yesterday's Emails"; // The Subject of the email
$email = "XXX@XXXX.com"; // Who the email is to
$headers = "From: ".$email_from;
mail($email, $email_subject, $message, $headers);
mysql_close($link);
?>


ygoutham: Thanks for noticing. I realized my blub after the post was submitted.

I cant find the unexpected $. SOO close.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi kpandelakis,

I suspect that this line is the culprit:

  $email_subject = "Yesterday's Emails"; // The Subject of the email

Escape the single quote:

$email_subject = "Yesterday\'s Emails"; // The Subject of the email



Also, you've got a mixture of single and double quoted text.  When you build $message, I suspect that you always want to use double quotes as you really want carriage returns and line feeds in the message, not "\r" and "\n".  Also, if you're formatting the text for unix, you don't need the carriage returns, just line feeds.  If you're formatting for Windows, you should pair the carriage returns and line feeds.  That is, "\r\n\n" becomes "\r\n\r\n".


Good Luck,
Kent
0
 
kpandelakisAuthor Commented:
YIPPY! well I made all of your suggestions and STILL ran in the parse error. In writing this post, I realized that I was missing a close curly bracket in the :

while ($row = mysql_fetch_row($result)) {
 foreach($row AS $col) {
 $message .=  $col . '\r\nr\\n';
}
}
I think I got it going now. I will run a test real quick!
0
 
kpandelakisAuthor Commented:
AHH Kent it is working like a charm!!
MANY Thanks!
I will certianly apply your bracket coding style into my projects. I totally see your point.
Have a most excellent day!
Koren
0
 
kpandelakisAuthor Commented:
OH, as an FYI the escaped quote in the subject line came through in the email. So, I just used the plain ole single quote there. GO FIGURE! Must be a single quote within the double quotes thing?
Thanks Again!
0
 
Kent OlsenData Warehouse Architect / DBACommented:

You're very welcome.


Kent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.