• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1120
  • Last Modified:

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!
0
kpandelakis
Asked:
kpandelakis
  • 5
  • 5
1 Solution
 
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
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi kpandelakis,

Yep.  :)  


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


0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
Kent OlsenData 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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now