querying a database using checkbox values [Archive]

Rachel

11-20-02, 04:27

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 a blank screen, so I have no error messages to work with either.

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";

my $sth = $dbh->prepare($sql);
$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;
}

Bernd Dulfer

11-21-02, 06:02

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 a blank screen, so I have no error messages to work with either.

Error messages should show up in the server error log.

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));

I think here is an error. $where_clause looks something like:

ResManageon and DriveCreativityon and DriveDecisionMakeon and DriveChangeOrienton

This is probably not what you want in a where clause.

Change the 'my $where_clause...' line to:

my $where_clause = join(' and ',map($_.= ' = "on"', @clauses));

$where_clause would then look like:

ResManage = "on" and DriveCreativity = "on" and DriveDecisionMake = "on" and DriveChangeOrient = "on"

Read the documentation of the CGI module. You can call a CGI program from the commandline and pass the necessary parameters by hand. This way you can test the program.