| Category: | Database |
| Author/Contact Info | agoth |
| Description: | A (I think) neat way of providing reports from the database with a minimum of fuss.
I've used inline HTML and no CGI methods cause its so simple. But effective......... |
#!/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>$_ </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;
}
|
|
|
|---|