>>>>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; <other stuff you might want to do> 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 <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> 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 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 ________________________________________________________________________ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/doc/ (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/doc/ (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
