young_grasshopper has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to create an email that will gather data from a database and send it out in email.

I have everything working(I tested) but only one problem, if the select finds 14 different entries in the table i get 14 emails.

How can I concatenate them into one email?

my $dbh = DBI->connect($dsn, $db_user_name, $db_password);
my $sth = $dbh->prepare(qq{
  select SYS, SSYS, OWNER, RESOLUTION, DES, DT, TM, TYPE, REMTYPE, CHA
+NGESTATUS, CURDATE()  from dbname where srep = 'yes' and DT >= CURDAT
+E()-7});

  $sth->execute;
  while(my $row = $sth->fetchrow_arrayref) {
  $i++;

  my ($SYS,$SSYS,$OWNER,$RESOLUTION,$DES,$DT,$TM,$TYPE,$REMTYPE,$CHANG
+ESTATUS,$CURDATE) = (@$row);
    %mail = ( To      => "$REPORTADDRESS",
              From    => 'StatusReport@healthsouth.com',
              SUBJECT => "Last Weeks Status Report",
              Message => "PROJECT: $SYS SEVERITY: $REMTYPE
TODAYS DATE: $CURDATE
TECH: $OWNER
DATE: $DT TIME: $TM
STATUS: $TRSTAT  SERVER: $SSYS
SUMMARY:
$DES

RESOLUTION:
$RESOLUTION
-------------------------------------------------------------------"
           );

    sendmail(%mail) or die $Mail::Sendmail::error;

    print "OK. Log says:\n", $Mail::Sendmail::log;
}

[download]

janitored by ybiC: Balanced <code> and <readmore> tags, minor format tweaks for legibility, retitle from "concatenate" for searchability and descriptiveness

Replies are listed 'Best First'.
Re: Concatenate mail text
by Zaxo (Archbishop) on Jan 28, 2004 at 21:22 UTC

    Move the %mail header generation and the sendmail part outside the fetch loop. Inside the loop, just say 

         $mail{'Message'} .= <<EREC;
PROJECT: $SYS SEVERITY:

$REMTYPE

TODAYS DATE: $CURDATE

TECH: $OWNER

DATE: $DT TIME: $TM

STATUS: $TRSTAT SERVER: $SSYS

SUMMARY:

$DES

RESOLUTION:

$RESOLUTION

-------------------------------------------------------------------

EREC

    [download]
    as the "concatenate" in your title suggests.

    After Compline,
    Zaxo

[reply]
[d/l]
Re: Concatenate mail text
by young_grasshopper (Initiate) on Jan 29, 2004 at 13:36 UTC
[reply]
[d/l]

Back to Seekers of Perl Wisdom