#!C:\Dwimperl\perl\bin\perl.exe
# PERL MODULES WE WILL BE USING
use CGI;
use DBI;
use DBD::mysql;
use CGI::Carp 'fatalsToBrowser';
use CGI qw(:standard);
# Config DB variables
our $platform = "mysql";
our $database = "test";
our $host = "localhost";
our $port = "3306";
our $tablename = "addressbook";
our $user = "root";
our $pw = "password";
our $q = new CGI;
our $message;
our @rows;
# DATA SOURCE NAME
$dsn = "dbi:mysql:$database:localhost:3306";
# PERL DBI CONNECT
$connect = DBI->connect($dsn, $user, $pw);
#Get the parameter from your html form.
$lname=$q->param('lname');
$fname=$q->param('fname');
$phone=$q->param('phone');
$email=$q->param('email');
$address=$q->param('address');
$zip=$q->param('zip');
$action=$q->param('action');
$lname1=$q->param('lname1');
$fname1=$q->param('fname1');
$phone1=$q->param('phone1');
$email1=$q->param('email1');
$address1=$q->param('address1');
$zip1=$q->param('zip1');
#Subroutines for each user function
sub addcontact{
$sql="INSERT INTO test.addressbook(last_name,first_name,address,zip,email,phone) values('$lname','$fname','$address','$zip','$email','$phone')";
$sth = $connect->prepare($sql)
or die "Can't prepare $sql: $connect->errstrn";
$rv = $sth->execute;
if ($rv == 1){
$message = q(Record has been successfully updated!!!);
}else{
$message = q(Error!!while inserting records);
}
}
sub search{
$sql="SELECT * FROM test.addressbook where last_name='$lname1' and first_name='$fname1'";
$sth = $connect->prepare($sql);
$sth->execute();
$ref = $sth->fetchall_arrayref;
foreach $row (@{$ref}){
push @rows, @$row;
}
$rows=@rows;
if ($rows<1){
$message = q(Error! Record not found);
}
return @rows;
}
sub deletecontact{
$sql="DELETE FROM test.addressbook where last_name='$lname1'";
$sth=$connect->prepare($sql);
$rd=$sth->execute;
if ($rd == 1){
$message = q(Record has been successfully deleted!!!);
}else{
$message = q(Error! Couldn't delete record);
}
}
sub update{
$sql="UPDATE test.addressbook SET last_name='$lname1',first_name='$fname1',address='$address1',zip='$zip1',email='$email1',phone='$phone1' WHERE last_name='$lname1' and first_name='$fname1'";
$sth = $connect->prepare($sql);
$sth->execute();
$ru=$sth->execute;
}
# Choose action Add/Search/Delete/Update to call function
if ($action eq "Add"){
addcontact()
}
elsif ($action eq "Search"){
search()
}
elsif ($action eq "Delete"){
deletecontact()
}
elsif ($action eq "Update"){
update()
}
else {
print " Got No parameters";
}
# Return to html page
my $URL = '/contactsform.html';
print $q->header(
-type=>"text/html");
#Print search results and ask for delete/update
print $q->start_html("Address Book"),
$q->p($message);
if ($action eq "Search"){
print
$q->h3("Search Results"),
$q->p("Last Name: $rows[1]"),
$q->p("First Name: $rows[2]"),
$q->p("Address: $rows[3]"),
$q->p("Zip: $rows[4]"),
$q->p("Phone: $rows[5]"),
$q->p("Email: $rows[6]"),
$q->start_form(),
$q->p(submit(-name =>'action',-value =>'Update'), submit(-name =>'action',-value =>'Delete')),
$q->end_form();
}
#Opens new form for updating entry
if ($action eq "Update"){
print $q->start_form(),
$q->p("Last Name", textfield(-name => 'lname1',-value => '$lname',)),
$q->p("First Name", textfield(-name => 'fname1',-value => '$fname')),
$q->p("Address", textfield(-name => 'address1',-value => '$address')),
$q->p("Zip", textfield(-name => 'zip1',-value => '$zip')),
$q->p("Phone", textfield(-name => 'phone1',-value => '$phone')),
$q->p("Email", textfield(-name => 'email1',-value => '$email')),
$q->p(submit("Update")),
$q->end_form();
}
print $q->p( qq!Back to Main Page! );
print $q->end_html;
####
ADDRESS BOOK
ADDRESS BOOK CONTACTS