How to e-mail database query results?

[varig77]

I would like to know how I can e-mail database query results. I currently use a similar script as below to display the results in HTML format. Thanks in advance for your help.

#!/usr/bin/perl

require "cgi-lib.pl";

use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
&ReadParse(*in);

$DBH = DBI->connect("dbi:Oracle:Somedatabase", "Username", "Password");

print &PrintHeader;

$STH = $DBH->prepare("select description, time_add, user_id from products");

$STH->execute;
while (@row = $STH->fetchrow_array) {
print << "EOF";
<div align="center">
<table border="0" width="850" id="table1" cellspacing="1">
<tr>
<td>$row[0]</td>
<td>$row[1]</td>
<td>$row[2]</td>
</tr>
</table>
</div>
EOF
}

$DBH->disconnect;
print &HtmlBot;

 

[1DMF]

Create the same HTML but use sendmail to send a HTML email instead of printing it to screen.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[varig77]

Sorry, I am totally new to Perl, so please excuse my ignorance. Can I use the sendmail function inside of the while statement? For instance, can I do it like in the code below? I found that I cannot access the elemets of the array outside the while statement.

while (@row = $STH->fetchrow_array) {

print MAIL <div align="center">;
print MAIL <table border="0" width="850" id="table1" cellspacing="1">;
print MAIL <tr>;
print MAIL <td>$row[0]</td>;
print MAIL <td>$row[1]</td>;
print MAIL <td>$row[2]</td>;
print MAIL </tr>;
print MAIL </table>;
print MAIL </div>;
}

 

[1DMF]

What you would do is make a variable = the HTML

while (@row = $STH->fetchrow_array) {

my $body = "<div align=\"center\">
<table border=\"0\" width=\"850\" id=\"table1\" cellspacing=\"1\">
<tr>
<td>$row[0]</td>
<td>$row[1]</td>
<td>$row[2]</td>
</tr>
</table>
</div>";

# call the mailer sub
my $subject= "Your Email Subject";
my $myadd = "my\@email.com";           
my $recipadd = "recipient\@email.com";

# Send Email
&send_mail($recipadd,$myadd,$subject,$body);

}

add this sub to your script and change the $SMTP_DOMAIN to the correct value.

###########################################################
# sendmail: sends an email using smtp over tcp/ip sockets #
###########################################################
sub send_mail {

#############################
# Use Net::SMTP For mailing #
#############################

use Net::SMTP;

#_[0] = To
#_[1] = From
#_[2] = Subject
#_[3] = Body Text
#_[4] = Bcc

$SMTP_DOMAIN = "127.0.0.1" # Make this the domain/ip of your mail server

my (@Bcc, @Eadds, @recip);

# Create new instance of SMTP
my $smtp = Net::SMTP->new($SMTP_DOMAIN, Debug => 0, Hello => $SMTP_DOMAIN,) || die "Can't open mail connection: $!";

# convert recipients to array from CSV
@recip = split(/\,/, $_[0]);

# add addressess together
push @Eadds, @recip;

# Check for Bcc
if($_[4]){
    @Bcc = split(/\,/, $_[4]);
    push @Eadds, @Bcc;
}

# Send Mail
$smtp->mail($_[1]);
$smtp->recipient(@Eadds);
$smtp->data();
$smtp->datasend("To: @recip\n");
$smtp->datasend("From: $_[1]\n");

if($_[4]){
    $smtp->datasend("Bcc: @Bcc\n");
}

$smtp->datasend("Subject: $_[2]\n");
$smtp->datasend("Content-type: text/html\n\n");
$smtp->datasend("$_[3]\n");
$smtp->dataend();
$smtp->quit();

}

hope it helps


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[varig77]

Thank you very much for your help. This is exactly what I was looking for

 

[1DMF]

no probs, works a treat for me also.

remember you can also pass it multiple recipients as CSV

my $recipadd = "my1\@email.com,my2\@email.com,my3\@email.com";

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[varig77]

I encounter one problem when I execute the script. I receive several e-mails at once (one e-mail for every row the query returns). I was wondering if there is a way to overcome this problem.

 

[1DMF]

can you show the code you are using so I can understand what is going on.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.