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

In reply to Perl - MySQL query question by peppiv

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.