RE: Generating an automatic e-mail via MySQL
>>>>I am interested as to why no one seems to be using the DBI/DBD-MySQL
perl modules?
Because it's twice as much code to do the same job. Why format your data
when the -H option to mysql can do it too?
Cheers,
A
-Original Message-
From: Karl Dyson [mailto:[EMAIL PROTECTED]]
Sent: 12 February 2003 17:43
To: Andrew Braithwaite; Dan Tappin; [EMAIL PROTECTED]
Subject: RE: Generating an automatic e-mail via MySQL
I am interested as to why no one seems to be using the DBI/DBD-MySQL perl
modules?
Something like:
==
#!/usr/bin/perl
use strict;
use warnings;
use diagnostics;
use DBI;
my $dbname = 'DBI:$driver:database=database;host=dbserver.blah.com';
my $dbuser = 'dbusername';
my $dbpass = 'dbuserpass';
my $dbd = 'mysql';
my @mailbody;
my $subject;
my $dbh = DBI->connect($dbname,$dbuser,$dbpass,$dbd) || die "Unable to
connect to database. $DBI::errstr.\n";
my $sth = $dbh->prepare("select x,y,z from table where blah order by x")
|| die "cannot prepare select. $DBI::errstr.\n";
$sth->execute || die "cannot execute select. $DBI::errstr.\n";
while(my($x,$y,$z) = $sth->fetchrow_array)
{
build up a body of a mail with something like
push @mailbody,"$x is $y of $z";
}
$sth->finish;
open(MAIL,"|sendmail $recipients") || die "cannot open sendmail\n"; print
MAIL "To: $recipients\n"; print MAIL "From: My_Script\n"; print MAIL
"Subject: $subject\n"; print MAIL "\n"; print MAIL @mailbody; print MAIL
".\n"; close MAIL;
(obviously you could have used Net::SMTP here, or qmail-inject)
$dbh->disconnect;
==========
Cheers,
Karl
-----Original Message-
From: Andrew Braithwaite [mailto:[EMAIL PROTECTED]]
Sent: 12 February 2003 17:19
To: 'Dan Tappin'; [EMAIL PROTECTED]
Subject: RE: Generating an automatic e-mail via MySQL
I use perl for this.
Something like this:
--
#!/usr/bin/perl
use MIME::Base64;
use Mail::Sendmail 0.77;
$email = '[EMAIL PROTECTED]';
$subject = 'some stuff here';
$entire_msg = `/usr/local/mysql/bin/mysql -H -e 'some query here'
somedatabase`; $mail_result = mail_out($email, $subject , $entire_msg);
sub mail_out {
local ($to,$sub,$msg) = @_;
local(%mail) = (
SMTP => $smtp_server,
from => '[EMAIL PROTECTED]',
to => $to,
subject => $sub,
Message => $msg
);
$mail{'Content-type'} = 'text/html';
$mail{'Return-path'} = '[EMAIL PROTECTED]';
return sendmail(%mail);
}
## Disclaimer: this code is meant as a guide only. No responsibility will
be assumed by author if it fails to compile, breaks, or blows up your server
etc.. having said that it should work :)
--
A
-Original Message-
From: Dan Tappin [mailto:[EMAIL PROTECTED]]
Sent: 12 February 2003 16:37
To: [EMAIL PROTECTED]
Subject: Generating an automatic e-mail via MySQL
Does any one have a suggestion on running a daily / weekly e-mail
notification based on results from a MySQL query?
I have a table with date sensitive rows. The idea that as rows become
stale (they were created / updated more than a week or month ago) the
owner of the row is sent an e-mail with a summary of the stale items or
even just a link back to a web page for updating.
The e-mail addresses would come from a related 'user' table. The stale
data table would have the 'user' id in a column for a JOIN statement.
I have a feeling that I will get a few 'buy a PERL book' suggestions
which is fine but I am wondering if there is another method. The same
server is running Apache and PHP. Could I run this from the command
line using PHP?
In the end I would just like a simple CRON script that runs each day to
send the notifications.
Any first thoughts?
Thanks,
Dan
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
Trouble u
RE: Generating an automatic e-mail via MySQL
I am interested as to why no one seems to be using the DBI/DBD-MySQL
perl modules?
Something like:
==
#!/usr/bin/perl
use strict;
use warnings;
use diagnostics;
use DBI;
my $dbname = 'DBI:$driver:database=database;host=dbserver.blah.com';
my $dbuser = 'dbusername';
my $dbpass = 'dbuserpass';
my $dbd = 'mysql';
my @mailbody;
my $subject;
my $dbh = DBI->connect($dbname,$dbuser,$dbpass,$dbd) || die "Unable to
connect to database. $DBI::errstr.\n";
my $sth = $dbh->prepare("select x,y,z from table where blah order by x")
|| die "cannot prepare select. $DBI::errstr.\n";
$sth->execute || die "cannot execute select. $DBI::errstr.\n";
while(my($x,$y,$z) = $sth->fetchrow_array)
{
build up a body of a mail with something like
push @mailbody,"$x is $y of $z";
}
$sth->finish;
open(MAIL,"|sendmail $recipients") || die "cannot open sendmail\n";
print MAIL "To: $recipients\n";
print MAIL "From: My_Script\n";
print MAIL "Subject: $subject\n";
print MAIL "\n";
print MAIL @mailbody;
print MAIL ".\n";
close MAIL;
(obviously you could have used Net::SMTP here, or qmail-inject)
$dbh->disconnect;
======
Cheers,
Karl
-----Original Message-
From: Andrew Braithwaite [mailto:[EMAIL PROTECTED]]
Sent: 12 February 2003 17:19
To: 'Dan Tappin'; [EMAIL PROTECTED]
Subject: RE: Generating an automatic e-mail via MySQL
I use perl for this.
Something like this:
--
#!/usr/bin/perl
use MIME::Base64;
use Mail::Sendmail 0.77;
$email = '[EMAIL PROTECTED]';
$subject = 'some stuff here';
$entire_msg = `/usr/local/mysql/bin/mysql -H -e 'some query here'
somedatabase`; $mail_result = mail_out($email, $subject , $entire_msg);
sub mail_out {
local ($to,$sub,$msg) = @_;
local(%mail) = (
SMTP => $smtp_server,
from => '[EMAIL PROTECTED]',
to => $to,
subject => $sub,
Message => $msg
);
$mail{'Content-type'} = 'text/html';
$mail{'Return-path'} = '[EMAIL PROTECTED]';
return sendmail(%mail);
}
## Disclaimer: this code is meant as a guide only. No responsibility
will be assumed by author if it fails to compile, breaks, or blows up
your server etc.. having said that it should work :)
--
A
-Original Message-
From: Dan Tappin [mailto:[EMAIL PROTECTED]]
Sent: 12 February 2003 16:37
To: [EMAIL PROTECTED]
Subject: Generating an automatic e-mail via MySQL
Does any one have a suggestion on running a daily / weekly e-mail
notification based on results from a MySQL query?
I have a table with date sensitive rows. The idea that as rows become
stale (they were created / updated more than a week or month ago) the
owner of the row is sent an e-mail with a summary of the stale items or
even just a link back to a web page for updating.
The e-mail addresses would come from a related 'user' table. The stale
data table would have the 'user' id in a column for a JOIN statement.
I have a feeling that I will get a few 'buy a PERL book' suggestions
which is fine but I am wondering if there is another method. The same
server is running Apache and PHP. Could I run this from the command
line using PHP?
In the end I would just like a simple CRON script that runs each day to
send the notifications.
Any first thoughts?
Thanks,
Dan
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
This email has been scanned for all viruses by the MessageLabs SkyScan
service. For more information on a proactive anti-virus service working
around the clock, around the globe, visit http://www.messagelabs.com
This email has been scanned for all viruses by
RE: Generating an automatic e-mail via MySQL
: Does any one have a suggestion on running a daily / weekly e-mail : notification based on results from a MySQL query? In your cron file do something like: echo "select * from table" | mysql [options] db | mail [EMAIL PROTECTED] If it's a more complex query: cat ~/query.sql | mysql [options] db | mail [EMAIL PROTECTED] It still doesn't solve your problem perfectly, since you can't set email addresses dynamicly in that manner. I think you need to write a litle more than an SQl query or even a cron task. Go with either Perl or PHP. For sending email you can use either "mail" or "sendmail". - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Generating an automatic e-mail via MySQL
I use perl for this.
Something like this:
--
#!/usr/bin/perl
use MIME::Base64;
use Mail::Sendmail 0.77;
$email = '[EMAIL PROTECTED]';
$subject = 'some stuff here';
$entire_msg = `/usr/local/mysql/bin/mysql -H -e 'some query here'
somedatabase`;
$mail_result = mail_out($email, $subject , $entire_msg);
sub mail_out {
local ($to,$sub,$msg) = @_;
local(%mail) = (
SMTP => $smtp_server,
from => '[EMAIL PROTECTED]',
to => $to,
subject => $sub,
Message => $msg
);
$mail{'Content-type'} = 'text/html';
$mail{'Return-path'} = '[EMAIL PROTECTED]';
return sendmail(%mail);
}
## Disclaimer: this code is meant as a guide only. No responsibility will
be assumed by author if it fails to compile, breaks, or blows up your server
etc.. having said that it should work :)
--
A
-Original Message-
From: Dan Tappin [mailto:[EMAIL PROTECTED]]
Sent: 12 February 2003 16:37
To: [EMAIL PROTECTED]
Subject: Generating an automatic e-mail via MySQL
Does any one have a suggestion on running a daily / weekly e-mail
notification based on results from a MySQL query?
I have a table with date sensitive rows. The idea that as rows become
stale (they were created / updated more than a week or month ago) the
owner of the row is sent an e-mail with a summary of the stale items or
even just a link back to a web page for updating.
The e-mail addresses would come from a related 'user' table. The stale
data table would have the 'user' id in a column for a JOIN statement.
I have a feeling that I will get a few 'buy a PERL book' suggestions
which is fine but I am wondering if there is another method. The same
server is running Apache and PHP. Could I run this from the command
line using PHP?
In the end I would just like a simple CRON script that runs each day to
send the notifications.
Any first thoughts?
Thanks,
Dan
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Generating an automatic e-mail via MySQL
On Wed, 12 Feb 2003, Dan Tappin wrote: > Does any one have a suggestion on running a daily / weekly e-mail > notification based on results from a MySQL query? > > I have a table with date sensitive rows. The idea that as rows become > stale (they were created / updated more than a week or month ago) the > owner of the row is sent an e-mail with a summary of the stale items or > even just a link back to a web page for updating. > > The e-mail addresses would come from a related 'user' table. The stale > data table would have the 'user' id in a column for a JOIN statement. I do something similar using a shell script run by cron. This pipes a query into mysql like this: cat mysql_command.list | mysql -u -p database where the file mysql_command.list is a plain text file containing the query which uses SELECT INTO OUTFILE to dump fields containing membership IDs, names and email addresses of all entries between two dates of a membership database into a CSV file. Then the script continues using sed and cut to create a mail message body and header from the data in this CSV file which can be piped into mail (/bin/mail, /bin/mailx or whatever you have on your system) or even directly into sendmail. It works fine. Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Generating an automatic e-mail via MySQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dan -- ...and then Dan Tappin said... % % Does any one have a suggestion on running a daily / weekly e-mail % notification based on results from a MySQL query? ... % % I have a feeling that I will get a few 'buy a PERL book' suggestions % which is fine but I am wondering if there is another method. The same % server is running Apache and PHP. Could I run this from the command % line using PHP? Sure, if you have the php CLI binary built. If which php tells you something like /usr/bin/php then you're in. Any script you write that would work from a web page would work from the command line. In either cae, all you need to write is a script that connects to the DB server and does a SELECT against your parameters and then generates the list for which you must get email addresses to send messages, and then shoot off some email. Shouldn't be too tough. % % In the end I would just like a simple CRON script that runs each day to % send the notifications. Yep. Sounds good. % % Any first thoughts? Buy a perl book ;-) It would be well worth it anyway. % % Thanks, % % Dan HTH & HAND mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+SnqSGb7uCXufRwARAibxAKDpqJ0oL7KyI2/2M0Qm+R53gzKFUACglJQE Z+gri8ggHeMYTsnvkWBwvBc= =H5O0 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Generating an automatic e-mail via MySQL
Does any one have a suggestion on running a daily / weekly e-mail notification based on results from a MySQL query? I have a table with date sensitive rows. The idea that as rows become stale (they were created / updated more than a week or month ago) the owner of the row is sent an e-mail with a summary of the stale items or even just a link back to a web page for updating. The e-mail addresses would come from a related 'user' table. The stale data table would have the 'user' id in a column for a JOIN statement. I have a feeling that I will get a few 'buy a PERL book' suggestions which is fine but I am wondering if there is another method. The same server is running Apache and PHP. Could I run this from the command line using PHP? In the end I would just like a simple CRON script that runs each day to send the notifications. Any first thoughts? Thanks, Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
