Using MySQL and a CGI Script

[isca45]

I am probably making this much harder that what it really is. What I am trying to do is query a table of information in a mysql database. I have gotten it to print information from one of the tables in the correct manner. However, I am not sure how to query the tables in order. Not sure if that is something I need to do in MySQL or do in the cgi-script.
This is my first time messing with MySQL and I have only "hacked" scripts before.. never written one from scratch. The fact that it is printing information from Table1 in the database is encouraging... but to now get it to query Table2 if no information is found in Table1 and Table 3 if no info is found in Table1 or Table2 is what I am having problems with.
This is my code:
<pre>
#!/usr/bin/perl
#

use DBI;
use CGI;



read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});

use POSIX qw(strftime);
$now = strftime "%a %b %e %H:%M:%S %Y", localtime;

@pairs = split(/&/, $buffer);

foreach $pair (@pairs) {
($name, $value) = split(/=/,$pair);
$value =~ tr/+/ /; #makes real spaces
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg; # converts ascii
$value =~ s/\n/ /g; #kills eolns
$value =~ s/\|/ /g; #substitute pipes with spaces...
$FORM{$name} = $value;
}




$dbname = "customerinfo:localhost";
$dbd = "mysql";
$dbusername = "username";
$dbpassword = "password";
$statement1 = "SELECT * FROM Table1 where state='$FORM{'state'}' AND npa='$FORM{'npa'}'";


$dbh = DBI->connect($dbname,$dbusername,$dbpassword,$dbd);
if (!$dbh) {
&emitHTML("Unable to connect to the database with the specified username and password.");
print STDERR "Error connecting $DBI::errstr\n";
exit;
}

$sth = $dbh->prepare($statement1 );
if (!$sth) {
&emitHTML("Unable to connect to the database with the specified username and password.");
print STDERR "Error connecting $DBI::errstr\n";
exit;
}

$sth->execute ;
if (!$sth) {
&emitHTML("Unable to connect to the database with the specified username and password.");
print STDERR "Error connecting $DBI::errstr\n";
exit;
}

#=========================================================== =======
# HTML-CGI
#=========================================================== =======
my $cgi_obj = new CGI;

# HTTP-Header (Perl 5 CGI Library)
print $cgi_obj->header( -type=>'text/html',
-expires=>'+1h');

# HTML-Header (Perl 5 CGI Library)
print $cgi_obj->start_html(-title=>'List',
-BGCOLOR=>'#FFFFFF',
-LINK=>'#0000AA',
-VLINK=>'#0000AA',
-ALINK=>'#CC0000',
-DTD=>'-//W3C//DTD HTML 3.2//EN'
);

while (@list = $sth->fetchrow_array ) {
foreach $list (@list) { tr/+/ /; chomp; }


print "<center>";
print "$list[2], $list[1], $list[3]-$list[4]-$list[5]";
print "</center>";


# HTML-Doc end
print $cgi_obj->end_html;

#=========================================================== =======


$sth->finish;
$sth2->finish;
$sth3->finish;

$dbh->disconnect;
$dbh2->disconnect;
$dbh3->disconnect;

sub emitHTML {
my($error_message, $title) = @_;
$title || ($title = "Submission Error");
$errorCGI = CGI->new();
print $errorCGI->header(),
$errorCGI->start_html({-bgcolor=>"#ffffff", -title=>$title}),
$errorCGI->h1($title),
$errorCGI->p($error_message),
$errorCGI->end_html();
}


}</pre>

[atlas]

I didn't look too carefully, but I think you want to use


$sth->rows()

since that returns the number of hits your query generated.

mjk@atlascgi.com