Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Re: Checkbox Query Revisited

by dingus (Friar)
on Nov 21, 2002 at 11:34 UTC ( [id://214742]=note: print w/replies, xml ) Need Help??


in reply to Checkbox Query Revisited

A bit more info might help...
However here are some comments on what I see

In general a cgi script has 3 separate phases. You need to debug each and they can be developed sort of separately.

Phase 1 is the form layout - in other words the request screen where you ask the user to enter what he is looking for. This can be completely separate script (or even static HTML) or it can be a subroutine in the same script as phases 2 and 3. This is where you create forms, add checkboxes etc etc. and if you don't have a phase 1 then you can rarely do anything useful.

Phase 2 is validating and processing the input received from the user, this includes cookies, extended path info and the query_string as well as any POSTed parameters. Phase 2 means making sure that you have got sane defaults, that the defaults get replaced only with valid alternatives and that any invalid alternatives get flagged as errors.

Phase 3 then takes the output of phase 2 and runs whatever work you want to do on it and prints the results nicely for the user. During development it is quite easy to create a dummy phase 3 only script which prints results based on input you set in the script.

When you say you get blank screens and have problems with checkboxes it is unclear whether you are referin to phase 1, phase 2 or phase 3. A blank screen in Phase 1 implies that you are not correctly doing your 
print $cgi->header, $cgi->start_html, $cgi->start_form etc.

[download]
A blank screen for the results may be because of a processing/validating error in phase 2. i.e. checboxes are not beong recognised as being set or it could be a bug in your phase 3 code that results in no DB hits no matter what happens. If you make a debug version of the script that prints the output of phase 2 before entering phase 3 you can see which one is your problem (hint Data::Dumper and s/</&lt;/g are useful things for debugging)

Finally I have noticed that if you have a checkbox parameter at phase 2 then CGI.pm returns 'on' if the checkbox is set but the parameter is completely omitted if the checkbox is clear. Thus you may need to have some defaul "off" lines that get replaced if a parameter exists and is set to 'on'.

Dingus

Enter any 47-digit prime number to continue.

Replies are listed 'Best First'.
Re: Re: Checkbox Query Revisited
by Rachel (Acolyte) on Nov 21, 2002 at 15:24 UTC
    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]
[reply]
[d/l]
[select]
      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]

In Section Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: note [id://214742]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others contemplating the Monastery: (7)
As of 2024-04-19 07:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found