Processing DBI query results

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

Hello all,

I have a complicated question and it is mainly about how to process the data after I get it out of the database.

Here's what I have....

I have a CGI that has about 7 checkboxes in it ( a checkbox group). When submitted it sends an array of the checkboxes that were checked in a variable.....

So if "Edte, Ecpp, Excdr and Eab" were checked, I'd get them back as the array values.

These values tell me which fields of my query are to be displayed, so I setup a hash to correspond to the field in the database, as a few of them represent more than one field....

%dbfield = (
                'Edte' => [DATE],
                'Ecpp'  => [CPP],
                'Excdr' => [XCDR],
                'Eab'   => [ACCESS_BTS, ACCESS_SECTOR, ACCESS_CHANNEL]
    );
[download]

Now, by doing something like the following, I could put the fields into an array and then give that array to one of the DBI functions and it would spit out the values of each of these fields.

foreach $thing (keys %dbfield) {
                print "the members of $thing are:\n";
                foreach (@{$dbfield{$thing}}) {
                        print "\t$_\n";
           }
   }
[download]

Ok, now that that is all said.....we can get to the actual problem.....

I need to perform caclulations on the values as they come out of the database....does anyone know of a way to automate this.

I've thought of:

#db connect, prepare, exectute goes here....

while($ref = $sth->fetchrow_hashref()) {

foreach $opt (@enable_opts) { #@enable_opts is the array of checkbox v
+alues
    
    #switch statement to perform calculations on values ($ref{'DATE'})
    }
}
[download]

Other than that, I'm not sure how to go about this.
Any help is appreciated....
Kevin

Comment on Processing DBI query results Select or Download Code

Replies are listed 'Best First'.
Re: Processing DBI query results by RMGir (Prior) on Jun 18, 2002 at 20:41 UTC
Is there any reason you can't perform your computations in the select statement? Are the values computed "by row", or are they aggregate computations? Your other alternative would be to set up a hash of checkboxes to subrefs, allowing you to just loop over the @enable_opts array and just invoke `$compute_h{$opt}->($ref);` [download] to get the computed values, without a switch statement being involved... -- Mike	[reply] [d/l]
Re: Processing DBI query results by rdfield (Priest) on Jun 19, 2002 at 13:13 UTC
Expanding a little on RMGir's post - you could conditionally build the SQL statement depending on the options selected and do all of the calculations/aggregates there. eg `$sql = "select "; $joiner = " "; foreach (@enable_opts) { $sql .= $joiner . $_ . "(column)"; $joiner = ","; } $sql .= " from table where column = ?"; #etc` [download] rdfield	[reply] [d/l]