[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[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;