#!/usr/bin/perl -w

=head1 AUTHOR

 me

=head1 DATE

 10/04/2001

=head1 report.pl

 reporting script 

=cut

use strict;
use CGI;
use DBI;

my $dbh = DBI->connect('dbi:mysql:cheese', 'user', 'password', {RaiseE
+rror => 1 });

#---- Initialise variables and semi-constants

my $q  = new CGI;

my $rep = $q->param('rep') || '';

my @shared = ('id', 'name', 'email');

my %sql = (
    'new_rooms'  => [  qq% select COLS from rooms, users where users.i
+d = rooms.users_id %, 
                       [@shared, 'title', 'description'], 
                       'Report of room suggestions'
                    ],
    'winners'    => [  qq% select COLS from users where is_winner = 'Y
+' %, 
                       \@shared,
                       'Report of competition winners'
                    ],
    'all_users'  => [  qq% select COLS from users %, 
                       \@shared,
                       'Report of all users (winners or not)'
                    ],
    'opted_in'   => [  qq% select COLS from users where optin = 'Y' %,
                       \@shared,
                       'Report of all users that have opted in for mai
+l'
                    ],
);

if (exists $sql{$rep}) {
    do_report($sql{$rep});
} else {
    show_buttons();
}

$dbh->disconnect;

exit;

=head1 do_report

 parameters - reference to query and column array
 returns    - nowt
 performs   - sql query and formats output

=cut

sub do_report {   

    my $ref  = shift;
    my $f = 0;

    my $cols = join ',', @{ $ref->[1] };
    my $sql  = $ref->[0];
    $sql =~ s/COLS/$cols/;

    my @tmp = map { "<TH>$_</TH>" } @{ $ref->[1] };
    my $html = $q->header;
    $html .= qq( <html><head><title>$ref->[2]</title></head><body>
<h3>$ref->[2]</h3>\n
<h4><a href="report.pl">Back to menu</a></h4>
<table border="1" cellpadding="2" cellspacing="2">\n<TR bgcolor="gray"
+>@tmp</tr>\n
    );

    my $sth = $dbh->prepare($sql);
    $sth->execute;

    while (my $ref = $sth->fetchrow_arrayref) {
         $f = 1;
         my @ary = map { "<TD>$_&nbsp;</TD>" } @$ref; 
         $html .= "<TR>@ary</TR>\n";
    }
    if (!$f) {
         $html .= '<TR><td colspan="' . scalar(@tmp) . '">NO ROWS FOUN
+D</td></tr>';
    }

    $html .= '</table></body></html>';

    $sth->finish;

    print $html;
}

=head1 show_buttons

 parameters - none
 returns    - nothing
 performs   - output of request page

=cut

sub show_buttons {

    my $html = $q->header;
    $html .= qq( <html>
<head> <title> Cheese Game Stats </title> </head>
<body>
<BR>
<h3>Report Options</h3>
<UL>
<LI><a href="report.pl?rep=new_rooms">Room Suggestions</a>
<LI><a href="report.pl?rep=winners">All Winners</a>
<LI><a href="report.pl?rep=all_users">All Users</a>
<LI><a href="report.pl?rep=opted_in">Opted In</a>
</UL>
</body>
</html>
);
    print $html;
}

[download]

In reply to DB reporting script by agoth

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.