CGI.pm OO vs. FO

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..);
[download]

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)";
}
[download]

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();
[download]

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

Comment on CGI.pm OO vs. FO Select or Download Code

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 ... } [download] 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	[reply] [d/l]
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	[reply] [d/l]