FW: Perl DBI question

[Steve Howard]

Sorry, I forgot to reply to all when I replied:


If I understand what you are needing to do, rather than running the select
statement multiple times, it would be much more efficient to use a group by
SQL statement? I can't tell for sure about that part from what you have
written. However, if each question is identifiable in the table, you could
get the whole thing in one pass through the table. I am guessing something
like this for a table structure (since 0 means no answer, I am guessing the
rest of the possible answers are weighted numerically:

CREATE TABLE Evaluation (
        QuestNo int,
        Value           int)

with that simple table (yours is probably more complex), you can get the
averages of all question in one pass through the table with a query like:

SELECT
        QuestNo,
        round(avg(Value, 2)
                FROM Evaluation
                WHERE Value != 0
                        GROUP BY QuestNo

That will return your two columns, one being the number of the question, and
the other being the

However, to make use of what is returned efficiently, you need to re-order
what else you are doing. You are using prepare right, but go ahead and
execute your statement handle, then bind the rows into variables. using the
above query as an example, I'll show you (untested, but I use the method
quite often). I am also assuming that you have made the connection to your
db server by this point in your script.



# use the qq{} quotation notation
# to make it easy to not interfere with quotes in your
# SQL query.

my $sql_statement = qq{SELECT
        QuestNo,
        round(avg(Value, 2)
                FROM Evaluation
                WHERE Value != 0
                        GROUP BY QuestNo};

my ($row, $questno, $value);

my $sth_avg_all = $dbh->prepare($sql_statement) || die "Can't prepare
\n$sql_statement\n$dbh::errstr\n";

$sth_avg_all->execute() || die "Can't
execute\n$sql_statement\n$dbh::errstr\n";

$sth_avg_all->bind_columns(undef, \$questno, \$value);

while ($row = $sth_avg_all->fetchrow_arrayref) {
        #do what you need with each returned row here
        }

There are several ways to get the variables into your script, but that is
one of the easiest. perldoc dbi gives some other nice examples, and just
monitoring the dbi users group will give you some other slick ideas. That is
just one of the easier ways to do it.

Hope this helps

Steve Howard



-----Original Message-----
From: Kolene Isbell [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 20, 2001 4:36 PM
To: [EMAIL PROTECTED]
Subject: Perl DBI question


I'm trying to print out statistics on a web page, based on an evaluation
form, where the person filling it out answers some questions with values
1-4.  These are then stored in a MySQL database.  I need to print out
averages for each question, and throw out any zeros (which means they
didn't select an answer for that question).  I know I can type out
multiple SELECT statements, but I'm sure it'd be easier to do it with
perl.  I'm sure that using an array is the way to go, but after that I'm
lost.  Here's what I have so far-

# Sub for averaging all questions for all classes
sub avg_all {

my $dbh = shift;
my $hashref = shift;
my %in = %{hashref};
my $output;

my $sth_avg_all = $dbh->prepare("select round(avg($$answer[0]), 2) from
evaluation where $$answer[0] != 0}"); #this is the statement I need to
repeat with different fields

# output

$output .= "<font face=arial size=+2 color=#008080>Internet Class
Evaluations</font><br><font face=arial size

$output .= "Did this class meet your expectations? $$answer[0]";

}
$sth_avg_all->execute;





--
Kolene