Re: Re: Checkbox Query Revisited

Sorry if I appear ignorant but I only started learning Perl 2 weeks ago, and it has been a steep learning curve so my understanding of how all the modules relate to each other is not as good as it could be. I would go away and research it a lot more thoroughly if I wasn't under quite a lot of time pressure and I haven't just come on here looking for an easy solution either. I have tried everything I can think of and I can use a text box to query my database but I am having a lot of difficulty using checkboxes. I know the values are being passed to the script as they are in the url and I know there is nothing wrong with the html output because I tested it with a more simple query. This is the html form;

<HTML><HEAD>
<BODY>
<FORM action=
method=get>
<H3>Select Drive Elements</H3>
<P><INPUT type=checkbox value=Initiative name=drive>Initiative</P>
<P><INPUT type=checkbox value=ResOrientation name=drive>Results Orient
+ation</P>
<P><INPUT type=checkbox value=Creativity name=drive>Creativity</P>
<P><INPUT type=checkbox value=ChangeOrientation name=drive>Change Orie
+ntation 
</P>
<P><INPUT type=checkbox value=DecisionMaking name=drive>Decision 
Making</P>Select Level: <SELECT name=SelectLevel> <OPTION value=Experi
+enced 
  selected>Experienced<OPTION value=Introduction>Introduction</OPTION>
+</SELECT> 
Manages People: <SELECT name=ManagesPeople> <OPTION value=Yes 
  selected>Yes<OPTION value=No>No</OPTION></SELECT> 
<P><INPUT type=submit value=View name=Query></P></FORM></BODY></HTML>
[download]

This is the script which SHOULD process it;

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

    use strict;
    use DBI;
    use CGI;
    #use CGI::Carp qw( fatalsToBrowser warningsToBrowser );
  
 
    #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($chec
+kbox));
       }

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

    my $sql = "SELECT ResType, ResLevel, ResManage, Details, Length, S
+ource, Cost, FurtherDetails 
          FROM ResourceSettings, Resources
          WHERE ((ResourceSettings.ResID = Resources.ResID) and
                 ($where_clause))
          if     ($where_clause)
          ORDER BY order by ResType, ResLevel, ResManage, 
                Details, Length, Source, Cost, FurtherDetails";

    print $cgi->p($sql);

    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 perc
+ents
        $string =~ s/[^A-Za-z0-9%]//g;
        return $string;
      }
[download]

Comment on Re: Re: Checkbox Query Revisited Select or Download Code

Replies are listed 'Best First'.
Re: Re: Re: Checkbox Query Revisited by dingus (Friar) on Nov 22, 2002 at 13:28 UTC
OK. I understand what is (or rather is not) happening. This DBI.pm documentation explains all the bits. Whats happening is that the call to `my $sth = $dbh->prepare($sql);` appears to be failing and hence you cannot run the immediate next command (sth->execute) because sth is not the correct object type. Why $dbh->prepare doesn't work is beyond me as I've never used DBI.pm but I suspect that is you add change the prepare line to `my $sth = $dbh->prepare($sql) \|\| die $dbh->errstr;` you'll get a more helpful error message. PS I recommend uncommenting the use CGI:Carp line at the top of the script for debugging. Dingus `Enter any 47-digit prime number to continue.`	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Re: Re: Checkbox Query Revisited
by dingus (Friar) on Nov 22, 2002 at 13:28 UTC

DBI.pm documentation

Whats happening is that the call to my $sth = $dbh->prepare($sql); appears to be failing and hence you cannot run the immediate next command (sth->execute) because sth is not the correct object type.

Why $dbh->prepare doesn't work is beyond me as I've never used DBI.pm but I suspect that is you add change the prepare line to my $sth = $dbh->prepare($sql) || die $dbh->errstr; you'll get a more helpful error message.

PS I recommend uncommenting the use CGI:Carp line at the top of the script for debugging.

Dingus

Enter any 47-digit prime number to continue.

[reply]
[d/l]
[select]