#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use CGI ':standard';
use CGI::Carp 'fatalsToBrowser';
my $firstname = param('name') || 'Alan';# remove || Alan when working
my $user = "user";
my $pw = "password";
my $source = "DBI:mysql:test;localhost;";
my $dbh = DBI->connect($source, $user, $pw,
{RaiseError => 1, PrintError => 1})
or die ("Error connecting $DBI::errstr");
print "Content-type:text/html\n\n";
print '
Search Results
';
# SQL
my $sth = $dbh->prepare("
SELECT e.id, firstname, lastname,
date_first_employed, annual_salary,
c.contact_id, email_address,
home_phone_number,
emergency_phone_number
FROM employees as e
LEFT JOIN contact_id as i
ON i.id = e.id
INNER JOIN employee_contacts as c
ON c.contact_id = i.contact_id
WHERE firstname = ? ");
$sth->execute( $firstname );
# Results
my $records = 0;
while ( my ($id, $firstname, $lastname,
$date_first_employed,$annual_salary,
$contact_id,$email_address,
$home_phone_number,
$emergency_phone_number) = $sth->fetchrow_array())
{
print "This is the information returned from your query:
Firstname = $firstname
";
++$records;
print "Employee ID: $id
First Name: $firstname
Last Name: $lastname
Date First Employed: $date_first_employed
Annual Salary: $annual_salary
Contact ID: $contact_id
Email Address: $email_address
Home Phone Number: $home_phone_number
Emergency Phone Number: $emergency_phone_number
";
}
$sth->finish( );
$dbh->disconnect( );
if ($records == 0)
{
print "Error: No Matches were found for your search
for Firstname = $firstname.
Did you include capital letters?
";
}
# Another Search
print 'Search for Name [ Alan Jake Jessica Jim ]
';