http://www.perlmonks.org?node_id=490729

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

Greetings monks,

After many hours of frequent frustration and alliteration I decided to ask for your collective advice. Here is the skinny.

I have a form with about 30 form elements including 2 multiple selects (location and domain). This form uses the method get. My script is to use CGI.pm to parse the data from CGI. I test each param from my form to create a single where statement, $where. This is use to create an SQL select statment. An example of the select statement could be, "SELECT * from mytable where server LIKE %someserv% AND (location = 'San Antonio' OR location = 'Austin')"

Now on to the question. I had implemeted a routine in several scripts to handle SQL insertions and updates automatically by parsing out the keys and values from CGI. Becasue of this I began toying with the object oriented functionality of CGI.pm. This would allow me to assign the CGI params and values to a hash(%IN) and A)do a 'delete $IN{submit};' or $IN{myparam} = $someval and B)not have to assign my 30 or so params using my $param = param("someparam") either with global scope or passing many of them to several subroutines.

I am able to get the Object Orinted functionality working using the following code:
use strict; use CGI; my ($cgi, $key, %IN); $cgi = new CGI; %IN = $cgi->Vars; #%IN = (param1 => value1, param2 => value2 , etc..);
Though I am having issues attempting to handle multiple selects. See the code below for a failed example.
if (defined $IN{location}) { $where .= "AND (location = '$IN{location}[0]'"; for my $i (@{$IN{location}}) { $where .= "$i" }; $where = "$where)"; }
Below is an example of one of my insert routines from my other scripts.

delete $IN{somekey}; delete $IN{somekey1}; delete $IN{somekey2}; delete $IN{somekey3}; $IN{somekey4} = "someval"; foreach $key (keys %IN) { $keys[$i] = $key; $value = $IN{$key}; $value = $dbh->quote($value); #removes quotes from the values $values[$i] = $value; $i++; } my $key_string = join(',',@keys); #joins array elements, and status a +nd adds a comma my $value_string = join(',',@values"); #joins array values, and stat +us, and adds a comma $sql = "$function $tablename ($key_string) values ($value_string)"; $sth = $dbh->prepare($sql); $sth->execute();

Now to end my rambling and ask the question.
What is the best way to handle the CGI from a form with a large amount of elements and with multiple selects? Should I use the object oriented functionality? If so how do I handle multiple selects?

Thanks in advance,
Weary hok_si_la

Replies are listed 'Best First'.
Re: CGI.pm OO vs. FO
by kutsu (Priest) on Sep 09, 2005 at 20:41 UTC

    I'd use OO when your going to need to call multiple params at different times/different lexical scopes (the creation of a global cgi variable to control when and where you need certain params), something like the code that follows:

    use CGI; my $cgi = $cgi->new; sqlstuff(); email_someone(); sub sqlstuff { my @locations = $cgi->param('locations'); my $sql = q(select * from table where server like '%someserver%' and + ); $sql .= sprintf "(%s)", join " or ", map { location='$_'" } @locatio +ns; #thanks to [blokhead] for the above #see below for explaination #if needed in existing %IN hash (with assuming other fields) $IN{location} = [ @locations ]; .... do stuff ... } sub email_someone { my $someone = $cgi->params('someone'); .... do emailing stuff here ... }

    There are other ways to do this, but having seen your whole script this looks like a good stepping point.

    As for your actually code Vars returns a hash while param returns an array (or scalar depending on how it's called) so if you use $cgi->Vars you should use $where .= "$_" for values %IN; (if you have multiple locations from checkboxes you want param as Vars will combine locations into a single string)

    Update: Replied to CB questions and sql creation explaination:
    sprintf "(%s)" : meaning insert string between ()
    join " or " : returns a joined string (using delimiter "\sor\s" for inserting
    map { location ='$_'"} @locations; : sets location='$location[0]..[$#location] as the array for join to join

    "Cogito cogito ergo cogito sum - I think that I think, therefore I think that I am." Ambrose Bierce

Re: CGI.pm OO vs. FO
by CountZero (Bishop) on Sep 10, 2005 at 20:21 UTC
    Somewhat tangential to your question, but have you considered looking at SQL::Abstract for building your SQL statement? You might be able to devise a direct mapping between the input from your form and the SQL::Abstract-data structures, so the WHERE-clause would literally build itself.

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law