Apart from these small mistakes
dbh->quote # missing $ on $dbh
$sth->fethrow_array() # missing c in fetch
the major problem is the SQL. For it to work you have to JOIN the tables and the type of JOIN, LEFT, RIGHT, INNER, OUTER etc depends on the design of your tables, essentially what the primary keys are. I've created a simplified working example of your code so you can experiment with different SQL statements ;
#!/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 '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Search Results</title>
</head>
<body>';
# 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 "<p>This is the information returned from your query: </p>
<pre>Firstname = $firstname</pre>";
++$records;
print "<pre>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</pre><hr />";
}
$sth->finish( );
$dbh->disconnect( );
if ($records == 0)
{
print "<p>Error: No Matches were found for your search
for Firstname = $firstname.
Did you include capital letters?</p>";
}
# Another Search
print '<b>Search for Name [ Alan Jake Jessica Jim ] </b>
<form action="" method="post">
<input type="text" name="name"/>
<input type="submit" value="Search"/>
</form>
</body></html>';
poj
