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

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!

20021119 Edit by Corion : Removed code tags from text

#!c:/perl/bin/perl.exe 

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

# handle any queries that have been sent our way 
my $initiative = validate($cgi->param('Initiative')); 
my $resorientation = validate($cgi->param('ResOrientation')); 
my $creativity = validate($cgi->param('Creativity')); 
my $changeorientation = 
validate($cgi->param('ChangeOrientation')); 
my $decisionmaking = validate($cgi->param('DecisionMaking')); 
my $level = validate($cgi->param('SelectLevel')); 
my $manage = validate($cgi->param('ManagesPeople')); 


if ($cgi->param('Query')) { 
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 DriveInitiative = $initiative" 
if ($initiative = 'on'); 
$sql .= "where DriveResOrient = $resorientation" 
if ($resorientation = 'on'); 
$sql .= "where DriveCreativity = $creativity" 
if ($creativity = 'on'); 
$sql .= "where DriveChangeOrient = $changeorientation" 
if ($changeorientation = 'on'); 
$sql .= "where DriveDecisionMake = $decisionmaking" 
if ($decisionmaking = 'on'); 
$sql .= "where ResLevel like $level"; 
$sql .= "where ResManage like $manage"; 
$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 ... maybe invalid?"; 

die "Error connecting: '$DBI::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; 
}

[download]

Replies are listed 'Best First'.
Re: querying a database using checkbox values
by Zaxo (Archbishop) on Nov 19, 2002 at 09:03 UTC

    Your $sql string is not correctly formed, for two reasons that I see offhand:

    • The strings you concatenate to form $sql do not have whitespace at the ends, so meaningful identifiers are being stuck together to make meaningless ones.
    • Your form data is inserted in $sql without the quoting required of values in SQL. You can quote them explicitly by using the DBI's quote method ( my $initiative = $dbh->quote(validate($cgi->param('Initiative'))); , for example), or else by using placeholders. There, you simply replace all the variables with question marks and supply the values as arguments to the statement handle's execute() method. The second technique is usually more convenient.

    After Compline,
    Zaxo

[reply]
[d/l]
      Zaxo, Thanks very much for your reply. I tried both of those tips you gave me, it still threw up a blank screen though. Any other ideas?
[reply]
        ColtsFoot, UnderMine, and rdfield have all made good observations I missed. Have you looked into their suggestions?

        That done, here are some debugging techniques.

        • (Added) Pick an indenting style and use it religiously. perlstyle is good dogma if you want help with that.
        • Turn on warnings with -w on the splatline or use warnings;
        • Run the script from the command line, giving the cgi form parameters as name=value pairs on the command line.
        • Once that works, redirect the html printed to a file and read that file with a browser. Check it for html compliance.
        • When ready to try it on the server, temporarily add use CGI::Carp qw(fatalsToBrowser warningsToBrowser); right after use CGI;. With that you can see errors live without having to go to the server error log.

        The key is to get the system to tell you what it thinks is wrong.

        I meant to comment earlier that your code shows great promise. There are lots of little errors, but your use of the correct modules in proper ways is a good sign. You are trying to do the right things, and you do nothing useless or wrong headed.

        After Compline,
        Zaxo

[reply]
[d/l]
[select]
Re: querying a database using checkbox values
by ColtsFoot (Chaplain) on Nov 19, 2002 at 10:18 UTC
    Rachel,

    I think you will find the you have falen into the =/==/eq trap in your if statements, they should read 
    $sql .= "where DriveInitiative = $initiative" 
if ($initiative eq 'on');

    [download]
    You could always try printing the $sql variable before you prepare it with something like 
    print qq($sql<BR>);

    [download]
[reply]
[d/l]
[select]
Re: querying a database using checkbox values
by UnderMine (Friar) on Nov 19, 2002 at 10:30 UTC
    OK here goes...
    A. if ($initiative = 'on') assigns variable and returns true it does not evaluate try  if ($initiative eq 'on')
    B. What are the checkboxes returning? would s/\W//g be more effective stripping non word charecters?
    C. If you are using checkboxes rather than radio boxes then multiple ones can be checked.
    my $hash ={ Initiative=>'DriveInitiative', 
            ResOrientation => 'DriveResOrient'};
     
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.FurtherDeta
+ils 
             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.FurtherDetail
+s";

    [download]

    Code is untested:(
    Hope this helps
    UnderMine
[reply]
[d/l]
[select]
      Thanks, I tried that and got this error Bad name after on'. This error was on line 34 which is this line; 
      my $where_clause = join(' and ',map($_.'= 'on', @clauses));

      [download]
      I thought this was due to the quotes not being closed at first but then I closed them and still got the same error so it seems like it doesn't like @clauses. Thanks to everyone who replied to my question.
[reply]
[d/l]
        Ok, I had an extra quote and now this has been sorted, I am back to my blank screen. Don't you just love life!?;)
[reply]
Re: querying a database using checkbox values
by rdfield (Priest) on Nov 19, 2002 at 10:54 UTC 
    $sth->execute || 
die "Could not execute SQL statement ... maybe invalid?"; 

die "Error connecting: '$DBI::errstr'";

    [download]
    doesn't produce much useful information. Put the errstr in the first die... 
    $sth->execute || 
die "Could not execute SQL statement ... " . $dbh->errstr;

    [download]

    rdfield

[reply]
[d/l]
[select]
Re: querying a database using checkbox values
by princepawn (Parson) on Nov 19, 2002 at 14:59 UTC
    form data to database query is a DBIx::Recordset one-liner... check it out!

    Carter's compass: I know I'm on the right track when by deleting something, I'm adding functionality

[reply]

Back to Seekers of Perl Wisdom