SQL -- getting one row problem -- clever mySQL trick

[David Gilden]

Hello out there...

I wanted to thank everyone who has kindly offered their assistance
with my current project.  Of course I have more questions,
But first I wanted to share how I solved my current issue.
It's displays a clever mySQL trick! -- see script below:

Dave Gilden
( kora musician / audiophile / web master @ cora connection /  Ft. Worth, TX, USA)

And here is a question for anyone who knows sendmail.

How can I add a 'CC' or even better a 'BCC' instead of 
using multiple email address comma delimited.


my $mailprog = '/usr/lib/sendmail';
my $recipient = '[EMAIL PROTECTED],[EMAIL PROTECTED]';

...some code...

sub send_mail {
my $data;
$email ||= '[EMAIL PROTECTED]';

open(MAIL, "|$mailprog -t");
print MAIL "To: $recipient\n";
print MAIL "From: $name <$email>\n";
print MAIL "Subject: $subject\n\n";
print MAIL "lots of text from the database\n";
close(MAIL);
}




MySQL TRICK
###

#!/usr/bin/perl -w

use DBI;
use strict;

... some stuff removed ...
my $subject = "Jakes's Lawncare Guestbook New Entry";

my ($id,$sql,$dbh,$sth,$datecreated,$name,$email,$comments);

&initialize_dbi;


 Read from Guestbook ###

### get and store MAX(id) -- the last entry-- via SQL
$sql = "SELECT [EMAIL PROTECTED]:=MAX(id) FROM $table_name;";
run_statement($sql);
$sql = "SELECT id, DATE_FORMAT(DATE_ADD(datecreated, INTERVAL 2 HOUR),
 '%c/%e/%y at: %l:%i %p'),email, name, comments FROM $table_name where id = [EMAIL 
PROTECTED];";
run_statement($sql);
($id,$datecreated,$email,$name,$comments) = $sth->fetchrow;
$sth->finish; 
#debug
#print "$id\n$datecreated\n$email\n$name\n$comments";
$dbh->disconnect; # close date base connection 


sub initialize_dbi{
   my $drh = DBI->install_driver( 'mysql' );
   $dbh = DBI->connect("DBI:mysql:$user_name:$sql_server", $user_name, $user_password);
   die "Cannot connect: $DBI::errstr\n" unless $ ;
}

sub run_statement{
my $stmt = shift;
$sth = $dbh->prepare($stmt);
$sth->execute;
}

__END__

-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

SQL -- getting one row problem

[David Gilden]

Good afternoon,

Here is the output of my script, and can’t understand why this is not working.
Thanks for any and all pointers!
Dave Gilden

[EMAIL PROTECTED]:~/cgi-bin$  mail_guest_to_jk.cgi 
Use of uninitialized value in concatenation (.) or string at ./mail_guest_to_jk.cgi 
line 33.

Use of uninitialized value in concatenation (.) or string at ./mail_guest_to_jk.cgi 
line 37.
DBD::mysql::st execute failed: You have an error in your SQL syntax near '' at line 1 
at ./mail_guest_to_jk.cgi line 60.
DBD::mysql::st fetchrow failed: fetch() without execute() at ./mail_guest_to_jk.cgi 
line 40.
Use of uninitialized value in concatenation (.) or string at ./mail_guest_to_jk.cgi 
line 41.
Use of uninitialized value in concatenation (.) or string at ./mail_guest_to_jk.cgi 
line 41.
Use of uninitialized value in concatenation (.) or string at ./mail_guest_to_jk.cgi 
line 41.
Use of uninitialized value in concatenation (.) or string at ./mail_guest_to_jk.cgi 
line 41.
Use of uninitialized value in concatenation (.) or string at ./mail_guest_to_jk.cgi 
line 41.



the script:
-
#!/usr/bin/perl -w

use DBI;
use strict;

## Sensitive stuff deleted :)

my ($sql,$dbh,$sth,$datecreated,$name,$email,$comments);

&initialize_dbi;


 Read from Guestbook ###

### get id for last entry  
$sql ="SELECT MAX(id) FROM $table_name;";

run_statement($sql);

my $id = $sth->{id};
print "$id\n";

$sth->finish; 

$sql = "SELECT id, DATE_FORMAT(DATE_ADD(datecreated, INTERVAL 2 HOUR), '%c/%e/%y at: 
%l:%i %p'),email, name, comments FROM $table_name where id = $id;";

run_statement($sql);
# get the one row selected
($id,$datecreated,$email,$name,$comments) = $sth->fetchrow;
print "$id\n$datecreated\n$email\n$name\n$comments";
$dbh->disconnect; # close date base connection 
exit;


sub initialize_dbi{
   my $drh = DBI->install_driver( 'mysql' );
   $dbh = DBI->connect("DBI:mysql:$user_name:$sql_server", $user_name, $user_password);
   die "Cannot connect: $DBI::errstr\n" unless $ ;
}


sub run_statement{
my $stmt = shift;
$sth = $dbh->prepare($stmt);
$sth->execute;
}

__END__




==
 Cora Connection: Your West African Music Source
  Resources, Recordings, Instruments & More!
    
==

-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]