Phonebook

#!/usr/bin/perl -wT

use strict;

use lib "/usr/local/custom/lib"; # A directory containing Perl modules.

use Utilities; # A particular module to use. (Utilities.pm)

use DBI;

use Term::ANSIColor qw(:constants);

# James Park

# Created on 2-01-11

# This creates a phonebook and stores it all in a database table





#############################################################



my $sth; # statement handle

my $name;

my $PhoneNumber;



#############################################################



# Get database username from unix username and database password from STDIN.

my $username = Utilities::username(); # Find unix username of the current user.

my $dbusername = $username;

my $dbname = $username;



print "Enter your database password: ";

use Term::ReadKey;

ReadMode 'noecho';

my $dbpassword = ReadLine(0);

print "\n";

ReadMode 'normal';

chomp $dbpassword;



#############################################################



# Open database and create database handle

my $dbh =

  DBI->connect("DBI:mysql:$dbusername;host=localhost;port=3306",

               "$dbname",

               "$dbpassword")

    or die "Can't connect: " . DBI->errstr();



# For postgress use: Pg instead of mysql, port 5432.



#######################





my %phonebook;

while (1) {

    my $choice = menu();

    if ($choice == 1) {

        addEntry();

    } elsif ($choice == 2) {

         delete_entry();

    } elsif ($choice == 3) {

        searchEntry();

    } elsif ($choice == 4) {

        showEntry();

    }elsif ($choice == 5) {

        clearTable();

    } elsif ($choice == 6 ) {

        exit();

    } else {

        print "Not a choice, please try again";

    }

}



###############################################



sub menu {

    print RED "1. Add an entry to your phonebook.\n";

    print  "2. Delete someone from your phonebook.\n";

    print  "3. Look up someones number.\n";

    print  "4. List all entries.\n";

    print  "5. Delete entire phonebook and start over.\n";

    print  "6. Close phonebook.\n";

    print  "Enter your choice (1-6): ";

    my $prompt = <STDIN>;

    return $prompt;

}



################################################



sub addEntry {



    print BLACK "Please enter their name:";

    chomp (my $name = <STDIN>);

    $name = lc($name);

    print"Please enter their phone number (123-4567):";

    chomp (my $phone = <STDIN>);

    if ($phone !~ m|^\d\d\d\D\d\d\d\d$|) {

    print "'$phone' is not in correct '123 4567' format.\n";

    return ;}

    my $sth; # statement handle

    $sth = $dbh->prepare('SELECT name FROM Phonebook WHERE name = ?');

    $sth->execute($name);

        my $ref=$sth->fetchrow_hashref();

    if (defined $ref->{'name'}){

        print "$name is already in the phonebook.\n";



        }

    else{

        $sth = $dbh->prepare('INSERT INTO Phonebook (name, Phone)           VALUES (?, ?)')

                or die "Couldn't prepare statement: " . $dbh->errstr;



            $sth->execute($name, $phone);

            print "name: $name at $phone have been added.\n";





    }

}



###############################################

sub delete_entry {

  print BLACK"If youre sure you want to DELETE this person enter their name> ";



  my $dbh =DBI->connect("DBI:mysql:jpark005;host=localhost;port=3306",

               "$dbname",

               "$dbpassword")



                or die "Couldn't connect to database: " . DBI->errstr;

        my $sth = $dbh->prepare('DELETE FROM Phonebook WHERE name = ?')

                or die "Couldn't prepare statement: " . $dbh->errstr;

  chomp (my $delname = <STDIN>);

  $delname = lc($delname);

  $sth->execute($delname)             # Execute the query

            or die "Couldn't execute statement: " . $sth->errstr;

  print "$delname was removed from your phonebook\n";

  $dbh->disconnect;

  $sth->finish;



}



##################################################





sub searchEntry {



    my $dbh =DBI->connect("DBI:mysql:jpark005;host=localhost;port=3306",

               "$dbname",

               "$dbpassword")



                or die "Couldn't connect to database: " . DBI->errstr;

        my $sth = $dbh->prepare('SELECT * FROM Phonebook WHERE name = ?')

                or die "Couldn't prepare statement: " . $dbh->errstr;



        print BLACK"Enter the name you would like to search for, hit<enter> to quit> ";



        while (my $name = <>) {               # Read input from the user

          my @data;

          chomp $name;

          $sth->execute($name)             # Execute the query

            or die "Couldn't execute statement: " . $sth->errstr;



          # Read the matching records and print them out

          while (@data = $sth->fetchrow_array()) {

            my $phone = $data[1];



            print "\t  $name:$phone  \n";

          }



          if ($sth->rows == 0) {

            print "No matching names were found.\n\n";

            $dbh->disconnect;





            return;



          }

          $sth->finish;

          print "\n";

          print "Enter name> ";





        }



 }







###################################################

sub showEntry{

    my $dbh =DBI->connect("DBI:mysql:jpark005;host=localhost;port=3306",

               "$dbname",

               "$dbpassword")

                or die "Couldn't connect to database: " . DBI->errstr;



        my $sth = $dbh->prepare('SELECT * FROM Phonebook')

                or die "Couldn't prepare statement: " . $dbh->errstr;



         $sth->execute()

            or die "Couldn't execute statement: " . $sth->errstr;

         my @data;

         while (@data = $sth->fetchrow_array()) {

        print BLACK "@data\n";

          }

          $sth->finish;

          $dbh->disconnect;









    }



############################################

sub clearTable{

    # Clear table "Phonebook"

    $sth = $dbh->prepare('DELETE FROM Phonebook')

             or die "Can't prepare SQL: " . $dbh->errstr();

    $sth->execute()

            or die "Can't execute SQL: " . $sth->errstr();

    $sth->finish();

    print BLACK "The Phonebook was cleared\n\n";

}



################################################## io::prompt