in reply to CGI/HTML checkboxes to SQL query

You need to dynamically add the $where_clause only if the checkboxes are set.

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

[download]
Hope it Helps
UnderMine

Replies are listed 'Best First'.
CGI/HTML checkboxes to SQL query
by Rachel (Acolyte) on Nov 23, 2002 at 14:33 UTC
    Hi Mate, Thanks very much for your help. it is appreciated. As you can see I am a complete Perl beginner!!! I still have errors. When I ran the perl script with your changes I can get results back and it works great. However, when I put some form values in from the query string i.e http://localhost/cgi-bin/drive2.pl?Initiative=Initiative&ResOrientation=ResOrientation etc. What I don't understand is how to integrate these into the SQL statement based on what has been chosen. I get the 
    error message <!-- warning: DBD::ODBC::st execute failed: [Microsoft][
+ODBC Microsoft Access Driver] Too few parameters. Expected 1. (SQL­07
+001)(DBD: st_execute/SQLExecute err=­1)

    [download]
    Again many thanks.. Rachel.
[reply]
[d/l]
      Looks like you have a placeholder in your SQL statement (a placeholder is a question mark - "?") and you're not supplying a value for it in your execute statement. There doesn't appear to be enough information in your posts for a fuller explaination - you should post all of the relevent information:
      • The actual SQL statement being executed
      • Any error messages generated by the DBI interface for your prepare and execute statements
      • Any parameters supplied to the execute statement.

      rdfield

[reply]
        Okay this is an update on the problem. The good news is that I no longer get any errors. However, the script just goes ahead and executes the sql statement but without taking the form values into account. I will put this on my scratch pad, if anyone wants to take a look...... I would appreciate this. Thanks, Rachel
[reply]

In Section Seekers of Perl Wisdom