#!/usr/bin/perl 

use strict;

use DBI;
use CGI;



my $DB_Host = 'localhost';
my $DB_Name = 'widgets';
my $DB_User = 'root';
my $DB_Pass = 'xyz123';



{  # begin main



  #--
  # Create a new CGI object to process the HTML form.

  my $cgi = new CGI;



  #--
  # Get the value of the "LastName" field from our HTML input form.

  my $lastNameToQuery = $cgi->param('LastName');



  #--
  # Get a connection to the database server, aborting on failure.

  my $dbh = openDBConnection($DB_Host, $DB_Name, $DB_User, $DB_Pass);

  if ( ! defined $dbh ) {
    printErrorPage("Could not open a connection to the database!");
    exit;
  }



  #--
  # Define the query, prepare it, execute it then get the results as an array of hash references.

  my $sql = "SELECT * FROM names WHERE Last = \"" . $lastNameToQuery . "\"";

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

  $sth->execute();

  my $queryResults = $sth->fetchall_arrayref({});



  #--
  # Print the results as an HTML table.
  #
  # Note: the {'Last'} and {'First'} found below refer to column names in the database table.

  print STDOUT "Content-Type: text/html\n";
  print STDOUT "\n";

  print STDOUT "<TABLE BORDER=\"1\">" . "\n";

  print STDOUT "<TR><TH>Last Name</TH><TH>First Name</TH></TR>" . "\n";

  foreach my $i (0 .. $#$queryResults) {
    print STDOUT "<TR><TD>" . $queryResults->[$i]->{'Last'} . "</TD><TD>" . $queryResults->[$i]->{'First'} . "</TD></TR>" . "\n";
  }

  print STDOUT "</TABLE>" . "\n";
  


  #--
  # Close our database connection.

  $dbh->disconnect();



}  # end main



# =========================================================================
#              F U N C T I O N    D E F I N I T I O N S
# =========================================================================



# -------------------------------------------------------------------------
# |
# | Function  : printErrorPage
# |
# | Parameter : $errorMessage = the error message to print
# |
# | Return    : none
# |
# | Comments  : Prints an HTML error message.  This prints the "Content-Type"
# |             HTTP header.
# |
# -------------------------------------------------------------------------

sub printErrorPage
{
  my $errorMessage = shift;

  print STDOUT "Content-Type: text/html\n";
  print STDOUT "\n";

  print STDOUT "<H1>" . $errorMessage. "</H1>\n";

}



# -------------------------------------------------------------------------
# |
# | Function  : openDBConnection
# |
# | Parameter : $host     = the name or ip of the database server
# | Parameter : $db       = the name of the database to connect to
# | Parameter : $username = the username to use when connecting to the MySQL server
# | Parameter : $password = the password to use when connecting to the MySQL server
# |
# | Return    : A handle to the database connection.
# |
# | Comments  :
# |
# -------------------------------------------------------------------------

sub openDBConnection
{
  my $host     = shift;
  my $db       = shift;
  my $username = shift;
  my $password = shift;

  my $dbh;  # Handle to a database connections.

  $dbh = DBI->connect("DBI:mysql:$db:$host",
                      $username, $password,
                      { RaiseError => 0, AutoCommit => 1 }
                     );

  if ( ! defined ($dbh) ) {
    return undef;
  }
  else {
    return $dbh;
  }

}