[Omaha.pm] Millionth SQL -> HTML table script

[Kenneth Thompson]

Nice.

An addition nicety might be to add the column names as headers-
especially if you don't know what the column names are when you start
(like select * from blah):

The reason for <td><b> instead of actual <th> is so that you could
potentially work in a 'print the columns names every x number of rows'
if you were going to dump a ton of rows.

Code:

sub print_SQL_and_results {
   my ($strsql) = @_;

   print "<pre>$strsql</pre>\n";
   print "<table>\n";
   my $sth = $dbh->prepare($strsql);
   $sth->execute;
   my @row;
   my $bHeadersPrinted;
   while (my $haref = $sth->fetchrow_hashref()) {
     my %hash = \$haref;
     if (!$bHeadersPrinted) {
       print "  <tr>";
       foreach my $attr (keys %$haref) {
         print "    <td><B>".$attr."</B></td>";
       }
       print "  </tr>";
       $bHeadersPrinted = "Yuppers";
     }
     print "  <tr>";
     foreach my $attr (sort keys %$haref) {
       print "    <td>".$haref->{$attr}."</td>";
     }
     print "  </tr>";
   }
   $sth->finish;
   print "</table>";
}

-----Original Message-----
From: omaha-pm-bounces at pm.org [mailto:omaha-pm-bounces at pm.org] On Behalf
Of Jay Hannah
Sent: Wednesday, August 17, 2005 9:22 AM
To: omaha-pm at pm.org
Subject: [Omaha.pm] Millionth SQL -> HTML table script

 
This is probably the millionth time I've written a 10 minute program to
dump the results of an SQL out to a web browser in an HTML table...

j



#!/usr/bin/perl

use strict;
use Omni::DB;
use CGI;
my $q = new CGI;

print 
   $q->header,
   "<h1>Select Rewards: Work Queue?</h1>\n";

my $dbh = Omni::DB::connect_prod();

my $strsql = <<EOT;
select p.name, re.rewardeventid, contactid, prop, eventid, reward,
  membershipid, givenname, middlename, surname, status_omni,
  status_program, comment, re.who_stamp, re.when_stamp
from reward_event re, programs p
where re.programid = p.programid
and re.programid IN (1,2,3,4)
and status_omni[1,1] IN ("0", "1", "3")
order by name, when_stamp
EOT
print_SQL_and_results($strsql);

$strsql = <<EOT;
select item_code, item_desc
from item_pick_defs
where item_type = 'SGARLN'
EOT
print_SQL_and_results($strsql);

exit;


sub print_SQL_and_results {
   my ($strsql) = @_;

   print "<pre>$strsql</pre>\n";
   print "<table>\n";
   my $sth = $dbh->prepare($strsql);
   $sth->execute;
   my @row;
   while (@row = $sth->fetchrow) {
      for (@row) { s/\s+$//; }
      print "<tr><td><nobr>";
      print join "</nobr></td><td><nobr>", @row;
      print "</td></tr>\n";
   }
   $sth->finish;
   print "</table>";
}


$dbh->disconnect;



_______________________________________________
Omaha-pm mailing list
Omaha-pm at pm.org
http://mail.pm.org/mailman/listinfo/omaha-pm