Hi,
I hope someone can help me with this. I am new to Perl and I am trying to use form values "checkboxes", to query an access database.
I am having trouble getting the sql statement to use the form values in the query. It passes the values from the form to the perl script but then throws up an error.
Bad name after on' at.........drive2.pl line 34.
This is the script I am "trying" to use to parse the form values and query the database (without much success I might add!)
#!c:/perl/bin/perl.exe -w
use strict;
use DBI;
use CGI;
#open connection to Access database
my $dbh = DBI->connect("dbi:ODBC:directory",
{ 'AutoCommit' =>1, 'RaiseError' =>1}) ||
die "Error connecting: '$DBI::errstr'";
# setup CGI handle
my $cgi = new CGI;
# start HTML
print $cgi->header . $cgi->start_html('Drive');
my $hash ={ Initiative=>'DriveInitiative',
ResOrientation => 'DriveResOrient',
Creativity => 'DriveCreativity',
ChangeOrientation => 'DriveChangeOrient',
DecisionMaking => 'DriveDecisionMake',
SelectLevel => 'ResLevel',
ManagesPeople => 'ResManage'};
my @clauses = ();
foreach my $checkbox (keys %$hash) {
push @clauses, $hash->{$checkbox} if validate($cgi->param($checkbox));
}
my $where_clause = join(' and ',map($_.'= 'on', @clauses));
my $sql = "select a.ResType, a.ResLevel, a.ResManage
a.Details, a.Length, a.Source, a.Cost, a.FurtherDetails
from Resources a
INNER JOIN ResourceSettings b ON a.ResID = b.ResID";
$sql .= " where $where_clause " if ($where_clause);
$sql .= " order by a.ResType, a.ResLevel, a.ResManage,
a.Details, a.Length, a.Source, a.Cost, a.FurtherDetails";
$sth->execute ||
die "Could not execute SQL statement ... " . $dbh->errstr;
my $rows = $dbh->selectall_arrayref($sql) || die $dbh->errstr;
if (@$rows) {
print "<table border=1 cellspacing=0 cellpadding=3><tr>" .
"<th>Type</th><th>Level</th><th>Manage</th><th>Details</th><th>Length</th><th>Source</th><th>Cost</th><th>Further Details</th></tr>";
foreach my $row (@$rows) {
print "<tr><td>" . join ("</td><td>", @$row) . "</td></tr>\n";
}
print "</table>\n";
}
else {
print "<p><i>No matches found</i></p>\n";
}
}
# disconnect from database
$dbh->disconnect();
exit(0);
# validate user input
sub validate {
my $string = shift;
# get rid of all non-letter, non-numerical characters and percents
$string =~ s/[^A-Za-z0-9%]//g;
return $string;
}
I hope someone can help me with this. I am new to Perl and I am trying to use form values "checkboxes", to query an access database.
I am having trouble getting the sql statement to use the form values in the query. It passes the values from the form to the perl script but then throws up an error.
Bad name after on' at.........drive2.pl line 34.
This is the script I am "trying" to use to parse the form values and query the database (without much success I might add!)
#!c:/perl/bin/perl.exe -w
use strict;
use DBI;
use CGI;
#open connection to Access database
my $dbh = DBI->connect("dbi:ODBC:directory",
{ 'AutoCommit' =>1, 'RaiseError' =>1}) ||
die "Error connecting: '$DBI::errstr'";
# setup CGI handle
my $cgi = new CGI;
# start HTML
print $cgi->header . $cgi->start_html('Drive');
my $hash ={ Initiative=>'DriveInitiative',
ResOrientation => 'DriveResOrient',
Creativity => 'DriveCreativity',
ChangeOrientation => 'DriveChangeOrient',
DecisionMaking => 'DriveDecisionMake',
SelectLevel => 'ResLevel',
ManagesPeople => 'ResManage'};
my @clauses = ();
foreach my $checkbox (keys %$hash) {
push @clauses, $hash->{$checkbox} if validate($cgi->param($checkbox));
}
my $where_clause = join(' and ',map($_.'= 'on', @clauses));
my $sql = "select a.ResType, a.ResLevel, a.ResManage
a.Details, a.Length, a.Source, a.Cost, a.FurtherDetails
from Resources a
INNER JOIN ResourceSettings b ON a.ResID = b.ResID";
$sql .= " where $where_clause " if ($where_clause);
$sql .= " order by a.ResType, a.ResLevel, a.ResManage,
a.Details, a.Length, a.Source, a.Cost, a.FurtherDetails";
$sth->execute ||
die "Could not execute SQL statement ... " . $dbh->errstr;
my $rows = $dbh->selectall_arrayref($sql) || die $dbh->errstr;
if (@$rows) {
print "<table border=1 cellspacing=0 cellpadding=3><tr>" .
"<th>Type</th><th>Level</th><th>Manage</th><th>Details</th><th>Length</th><th>Source</th><th>Cost</th><th>Further Details</th></tr>";
foreach my $row (@$rows) {
print "<tr><td>" . join ("</td><td>", @$row) . "</td></tr>\n";
}
print "</table>\n";
}
else {
print "<p><i>No matches found</i></p>\n";
}
}
# disconnect from database
$dbh->disconnect();
exit(0);
# validate user input
sub validate {
my $string = shift;
# get rid of all non-letter, non-numerical characters and percents
$string =~ s/[^A-Za-z0-9%]//g;
return $string;
}