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

I have a MYSQL database that I query using Perl and all its excellent DBI capabilities. The database is a compilation of surveys we take online. I'm building a reporting page that summarizes the surveys and I'm sure there's a much better way to do it.

Here's a sample of what I'm doing.

     my $sth = $dbh->prepare('SELECT COUNT(*) FROM owner WHERE rent_ca
+r = "Y"'); 
     $sth->execute() or die $sth->errstr; 
     my $rent_car = $sth->fetchrow_array();
     my $percent = ($rent_car / $result)*100;
        printf qq(<tr><td>Yes</td><td>$rent_car</td><td align="right">
+%.2f%%</td></tr>\n),$percent;
     
     my $sth = $dbh->prepare('SELECT COUNT(*) FROM owner WHERE rent_ca
+r = "N"'); 
     $sth->execute() or die $sth->errstr; 
     my $rent_car2 = $sth->fetchrow_array();
     my $percent2 = ($rent_car2 / $result)*100;
        printf qq(<tr><td>No</td><td>$rent_car2</td><td align="right">
+%.2f%%</td></tr>\n),$percent2;
               
     my $rent_carno = $rent_car + $rent_car2;
     my $rent_carnor = $result - $rent_carno;
     my $percent3 = ($rent_carnor / $result)*100;
        printf qq(<tr><td>No Response</td><td>$rent_carnor</td><td ali
+gn="right">%.2f%%</td></tr>\n),$percent3;

[download]
Obviously, I want (for formatting purposes) to write each response to it's own table row (three columns wide).

I know I should be using the 'do' command. I'm sure there's some way to this with lists or arrays that would be much quicker and cleaner. Can someone help me optimize this cumbersome code?

Also, I have some answers in the database that are not in the list of selections. Is there an easy way to query the MySQL for all values in a column and print/list them to the page?

peppiv

I'm on FreeBSD-Perl 5-MySQL 3.23

I'm also on milk, but that's another story

Replies are listed 'Best First'.
Re: Perl - MySQL query question
by gmax (Abbot) on Mar 08, 2002 at 16:20 UTC
    The do command is only for queries that do not return a result.
    Rather than giving you some of my examples, I can address you to jeffa's excellent tutorial on HTML::Template.
    It tells you exactly how to do what you need using selectall_arrayref.

    update (I hit the submit button instead of preview) :(
    As for you last question, I am not sure I got it correctly, but to get all the values from a column you can use this (untested) paradigm:
    my $column = "rent";
my $rows= dbh->selectall_arrayref(qq{SELECT DISTINCT $column FROM owne
+r});
for (@$rows) {
    print "@$_\n"
};

    [download]
     _  _ _  _  
(_|| | |(_|><
 _|

    [download]
[reply]
[d/l]
[select]
Re: Perl - MySQL query question
by talexb (Chancellor) on Mar 08, 2002 at 16:33 UTC
    I would not use the cut-and-paste method that you've used -- make an array of the response that you want to count, and count those up. I would also count up all of the responses. What's left over is the 'other'.

    Something like this (not tested code): 

      my $BaseQuery " select count(*) from owner";
  my $WhereClause = " where rent_car = '?'";

  #  Get the total number of rows in the table.

  my $sth = $dbh->prepare ( $BaseQuery );
  my $Total = $sth->fetchrow_array();

  #  Initialize a total in order to be able to calculate
  #  "Other" responses"

  my $SoFar = 0;

  #  Prepare the longer query.

  $sth = $dbh->prepare ( $BaseQuery . $WhereClause );

  #  Loop through valid responses.

  my %Responses = ( Y => "Yes", N => "No" );
  foreach ( keys %Responses )
  {
    $sth->execute ( $_ );
    my $ThisChoice = $sth->fetchrow_array();

    $SoFar += $ThisChoice;
    my $Result = sprintf ( "%3.1f%%", $ThisChoice / $Total );

    print qq ( <tr><td>$Responses{ $_ }</td><td>$Result</td></tr> );
  }

  #  Figure out how many "Other" responses there were.

  my $Result = sprintf ( "%3.1f%%", ( $Total - $SoFar ) / $Total );

  print qq ( <tr><td>Other</td><td>$Result</td></tr> );

    [download]

    --t. alex

    "There was supposed to be an earth-shattering kaboom!" --Marvin the Martian

[reply]
[d/l]
Re: Perl - MySQL query question
by fuzzyping (Chaplain) on Mar 08, 2002 at 17:05 UTC
    I think this will work. You will find that using placeholders (the "?" in the $query statement) will make your DBI programming life much easier.

    It may not look much shorter than yours, but it's infinitely more scalable.

    -fuzzyping
    my %rent_hash = (Y=>["Yes",undef], N=>["No",undef]);
my $query = "SELECT COUNT(*) FROM owner WHERE rent_car = ?";
my $sth = $dbh->prepare($query);
foreach (keys %rent_hash) {
        $sth->execute($_);
        my $rent_hash{$_}[1] = $sth->fetchrow_array();
        my $rent_carno += $rent_hash{$_}[1];
        my $percent = (($rent_car{$_}[1] / $result) * 100);
        printf qq(<tr><td>$rent_hash{$_}[0]</td><td>$rent_car{$_}[1]</
+td><td align="right">%.2f%%</td></tr>\n),$percent;
}

my $rent_carnor = $result - $rent_carno;
my $percent_total = (($rent_carnor / $result) * 100);
printf qq(<tr><td>No Response</td><td>$rent_carnor</td><td align="righ
+t">%.2f%%</td></tr>\n),$percent_total;

    [download]
[reply]
[d/l]
Re: Perl - MySQL query question
by mugwumpjism (Hermit) on Mar 08, 2002 at 16:30 UTC

    you probably want to combine your two queries into a single one:

    SELECT rent_car,count(rent_car) FROM owner GROUP BY rent_car
[reply]
[d/l]

Back to Seekers of Perl Wisdom