I should have stated more clearly -
This is a Perl Module I'm writing. The table already exists and I'm just trying
to pull the data out and run certain stats on them, one of which is the
averages. If I write it like "Select avg($$answer[0]), avg($$answer[1],
avg($$answer[2]) from
evaluation where $$answer[0] != 0 || $$answer[1] !=0 || $$answer[2] != 0" I'll
get false answers, because of the not equal to zero part. If I use and's
instead of or's, it's likely I'll get no data returned. So, I simply need to
have multiple select statements, which in this case about 20. So it'd be much
smother to write it once and put it a foreach loop or something.
I can see where you're going with what you've written below. I'll give it a
shot.
And yes, I've already connected to the db. This is just the code for the sub.
Kolene
Steve Howard wrote:
> 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
