#!/opt/bin/perl -w
#
use strict;
use DBI;
use CGI;

my $cgi = CGI->new();
my $paygroup_num = $cgi->param('paygroup_num');

if ($paygroup_num) {
	print $cgi->header(),
          $cgi->start_html("sample cgi page 1");
	print "<table>";
	
    display_paygroup_page($cgi, $paygroup_num);

	print "</table>";
	$cgi->end_html();
} else {
    show_form($cgi);
};


########
# SUBS BELOW
########

sub display_paygroup_page {
    
    
    my ($cgi, $paygroup_num) = @_;
my $match = pop(@_);
print "<tr><td> City Name </td><td>  Last Name </td><td> First Name </td></tr>\n";
	

## Connect to database
my $dbh = DBI->connect('dbi:mysql:adatabase', 'undef', 'undef')
            || die "couldn't connect to database";

## Get the data            
my $sql ='SELECT city, last_name, first_name FROM employees ORDER BY city';
my $sth = $dbh->prepare($sql);

$sth->execute();

## worked through data 
my($last_name, $first_name, $city, $paygroup_num1);
$sth->bind_columns(\($city, $last_name, $first_name, $paygroup_num1));

while($sth->fetch()){
	if($match =~ $paygroup_num1){
	print"<tr><td> $city </td><td> $last_name </td><td> $first_name </td></tr>\n";
	}
}
   
$sth->finish();   
$dbh->disconnect();
};


sub show_form {
    my $cgi = shift;

    print $cgi->header(),
          $cgi->start_html("Please specify an employee number"),
          $cgi->start_form(),
          "Enter an employee pay level number: ", 
          $cgi->textfield(-name => 'paygroup_num'),
          $cgi->submit(),
          $cgi->br(), "(for example: S-32 or H-22)",
          $cgi->end_form(),
          $cgi->end_html();

};

##</code><code>##

__DATA__
City Name Last Name First Name
Boston     Ma	   Rebecca
Boston	 Kent	 Orin
Boston	 Austin	James
Newark	 Smith	 Jessica 
Newark 	Clark	 Benjamin
Seattle    Jones	 Matt
Seattle    Jones	 George 



__END__

##</code><code>##

__DATA__
City NameLast NameFirst Name
Boston	 Austin	James
Boston	 Kent	  Orin
Boston	 Ma	    Rebecca
Newark	 Clark     Benjamin
Newark	 Smith     Jessica
Seattle	Jones	 George
Seattle	Jones	 Matt

__END__