Main Project Page

Relational databases are used in many large websites to quickly sort through information. In this case, the amount of information is more easily managable;
when dealing with larger amounts, however, relational databases can be quite useful for organization.

Below are the tables for which you can search for information from further down:

employees
id firstname lastname annual_salary date_first_employed
1 Alan White 100000 2004-09-03
2 Jake Michaels 80000 2007-11-18
3 Jessica Stirling 120000 1999-03-28
4 Jim Schmim 60000 2012-05-17


employee_contacts
contact_id email_address home_phone_number emergency_phone_number
Alan W. Alan@mail.com 650-939-8504 650-249-7128
Jake M. Jake@mail.com 650-421-7730 650-855-2217
Jessica S. Jessica@mail.com 650-419-2350 650-273-8530
Jim S. Jim@mail.com 650-769-8230 650-289-6530


The tables above are linked by this following table:



table_link
id contact_id
1 Alan W.
2 Jake M.
3 Jessica S.
4 Jim S.

The following fields can be used to search for information
found in the tables above:


Employee name:



Employee id:



Employee email address:



Date first employed:

#!/usr/bin/perl use DBI; use warnings; my $firstname = $in{name}; my $db = "project_database"; my $host = "localhost"; my $user = "root"; my $pw = "steampunk333"; my $source = "DBI:mysql: database = project_database; host = localhost;"; my $contactid; my $dbh; print "Content-type:text/html\n\n"; $dbh = DBI->connect($source,$user,$pw) or die "Can't connect to database: $DBI::errstr\n"; my $qfirstname = dbh->quote($firstname); if ($firstname eq "Alan") {$contactid = "Alan W.";} elsif ($firstname eq "Jake") {$contactid = "Jake M.";} elsif ($firstname eq "Jessica") {$contactid = "Jessica S.";} elsif ($firstname eq "Jim") {$contactid = "Jim S.";} else {$contactid = "0"; print "


Error: No Matches were found for your search. Did you include capital letters?



";} if($contactid ne "0") {print "

This is the information returned from your query:



";} my $qcontactid = dbh->quote($contactid); my $sth = $dbh->prepare("SELECT lastname, date_first_employed, annual_salary FROM employees WHERE firstname = $qfirstname, SELECT * FROM employee_contacts WHERE contact_id = $qcontactid"); $sth->execute( ); while (($lastname, $date_first_employed, $annual_salary, $date_first_employed, $email_address, $home_phone_number, $emergency_phone_number) = $sth->fethrow_array()) { print "

Employee ID: $id

Last Name: $lastname

Date First Employed: $date_first_employed

Annual Salary: $annual_salary

Contact ID: $contactid

Email Address: $email_address

Home Phone Number: $home_phone_number

Emergency Phone Number: $emergency_phone_number



"; } $sth->finish( ); $dbh->disconnect( );