Testing to see if a MySQL query is true

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

I wrote the quick, poorly-written, yet functioning code below to interface with a database of appointments. The intent of the script is to send me a notice via email a day before the event occurs. It doesn't seem to mater what I do, I can't make the script send me an email ONLY when the query is true. I'm open to any ideas.

#!/usr/bin/perl
use Date::Manip;
use DBI;
use strict;

my $mailprog = '/usr/lib/sendmail';

my $tomorrow=&DateCalc("today","+1day");

my $year = substr $tomorrow, 0, 4;
my $month = substr $tomorrow, 4, 2;
my $day = substr $tomorrow, 6, 2;
my $tomorrow = $year."-".$month."-".$day;
my ($dsn) = "DBI:mysql:andrewkmitchell_com:localhost"; # data source n
+ame
my ($user_name) = "akm2";  # user name
my ($password) = "merlin2562";         # password
my ($dbh, $sth);             # database and statement handles
my (@ary);                   # array for rows returned by query

# connect to database
$dbh = DBI->connect ($dsn, $user_name, $password, { RaiseError => 1 })
+;

# issue query
my $query = qq{ SELECT id, name, event_date, start_time, end_time, des
+cription
                FROM ltw_events
                WHERE event_date="$tomorrow" };
$sth = $dbh->prepare ( $query );
$sth->execute ();
open(MAIL,"|$mailprog -t");
print MAIL "To: akm2\@merlin-internet-services.net\n";
print MAIL "From: root\@merlin-Internet-Services.net (Re-minder)\n";
print MAIL "Subject: Reminder for Tomorrow ($tomorrow)\n";
print MAIL "X-Priority: 1 (Highest)\n\n";
print MAIL "This in an automatic message generated by Re-Mind.  The pu
+rpose of this message is to remind you of event(s) you have scheduled
+ for tomorrow ($tomorrow).\n\nEvents are listed below:\n\n";
while (@ary = $sth->fetchrow_array ())
{
        print MAIL join ("\t", @ary), "\n";
}
print MAIL "\nEND OF SCHEDULED EVENTS!\n";
close(MAIL);
$sth->finish ();

$dbh->disconnect ();
exit (0);
[download]

Andrew Kenton Mitchell
Andrew@AndrewKMitchell.com

Comment on Testing to see if a MySQL query is true Download Code

Replies are listed 'Best First'.
Re: Testing to see if a MySQL query is true by runrig (Abbot) on Oct 22, 2002 at 21:56 UTC
A 'query' is not true or false; it returns results (or no results). But you can do your open and initial print's in the first iteration of your while loop. That way if there are no results, you don't print anything. After the loop, you can print any final line(s) and close the pipe if there were any results.	[reply]
(tye)Re: Testing to see if a MySQL query is true by tye (Sage) on Oct 22, 2002 at 22:59 UTC
The phrase "in the first iteration of your while loop" made me think of something other than the way I would do it, but the idea is at least very similar. Like this: `$sth->execute(); my @ary= $sth->fetchrow_array(); if( @ary ) { open(MAIL,"\|$mailprog -t"); print MAIL ... do { print MAIL join ("\t", @ary), "\n"; } while( @ary = $sth->fetchrow_array() ); print MAIL "\nEND OF SCHEDULED EVENTS!\n"; close(MAIL); } $sth->finish();` [download] - tye (at least very similar to "Tye")	[reply] [d/l]
Re: (tye)Re: Testing to see if a MySQL query is true by runrig (Abbot) on Oct 23, 2002 at 01:04 UTC
I'm going to go out on a limb and assume akm2 doesn't have a million appointments a day, in which case we may as well use selectall_arrayref: `my $results = $dbh->selectall_arrayref($sql_statement); if (@$results) { open... print... print ... join("\t", @$_),"\n" for @$results; print... close... }` [download]	[reply] [d/l]
Re: Testing to see if a MySQL query is true by BUU (Prior) on Oct 22, 2002 at 23:30 UTC
$sth->execute will return the number of rows it affects, however it its valid SQL it will return 0E0 instead of 0	[reply]
Re: Testing to see if a MySQL query is true by nedals (Deacon) on Oct 23, 2002 at 00:35 UTC
I would suggest you move your 'while' loop out of MAIL and put the data into a $message variable and `print MAIL "$message\n";` If $message contains something, send the email. On a side: I hope that the $dsn, $user_name, and $password data are not real.	[reply] [d/l]
Re: Testing to see if a MySQL query is true by thor (Priest) on Oct 23, 2002 at 12:30 UTC
BTW: I would construe the always sending of e-mail as a feature. That way, you know for a fact that nothing happened to your program. Just think, the first time that you don't get that e-mail, you will be checking to see if it ran properly or not. I know I would. thor	[reply]


Perl-Sensitive Sunglasses
	PerlMonks