Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    1

    Unanswered: querying a database using checkbox values

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

  2. #2
    Join Date
    Sep 2002
    Location
    Germany, near Aachen
    Posts
    120

    Re: querying a database using checkbox values

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •