Personnel Information System in Perl and MySQL

I wrote these simple crud applications that I called Personnel Information System in Perl and MySQL that can add, edit, update, view and delete records of personnel. It is a menu-driven program that is written in console-based in Perl and the backend in MySQL database I hope you will find my work useful.

I am currently accepting programming work inventory system, enrollment system, accounting system, payroll system, information system, website design and development using WordPress, IT projects, school and application development, programming projects, thesis, and capstone projects, IT consulting work, computer tutorials, and web development work kindly contact me in the following email address for further details. If you want to advertise on my website kindly contact me also in my email address also. Thank you.

My email address is the following jakerpomperada@gmail.com, jakerpomperada@aol.com, and jakerpomperada@yahoo.com.

My mobile number here in the Philippines is 09173084360.

My telephone number at home here in Bacolod City, Negros Occidental Philippines is +63 (034) 4335675.

Here in Bacolod City, Negros Occidental I also accepting computer repair, web development using WordPress, Computer Networking and Arduino Project development at a very affordable price.

My personal website is http://www.jakerpomperada.com

My programming website is http://www.jakerpomperada.blogspot.com

I am also a book author you can purchase my books on computer programming and information technology in the following links below.

https://www.mindshaperspublishing.com/

https://www.unlimitedbooksph.com/

Sample Program Output

menu.pl

# menu.pl

# Author   : Jake R. Pomperada,MAED-IT,MIT

# Date     : April 32, 2020   Tuesday   1:03 PM

# Location : Bacolod City, Negros Occidental

# Email    : jakerpomperada@gmail.com

# Websites : http://www.jakerpomperada.com

#            http://www.jakerpomperada.blogspot.com

use DBI;

$myConnection = DBI->connect('DBI:mysql:records:localhost','root','');

# infinite loop for menu

while ( 1 )

{

printf("\n\n");

printf("\t\t===== MAIN MENU ======\n\n");

printf"\tPersonnel Information System in Perl and MySQL\n\n";

printf("\tCreated By Mr. Jake R. Pomperada,MAED-IT,MIT\n");

printf("\n");

print "\t[1] Add Personnel Record\n";

print "\t[2] Update Personnel Record\n";

print "\t[3] Delete Personnel Record\n";

print "\t[4] View Personnel Record\n";

print "\t[5] Quit Program\n\n";

printf("\tSelect the number in the menu : ");

my $option=<STDIN>;# get input

chomp($option);# remove "enter key"

# redirect options

if ( $option == 1 )

{

# set the value of your SQL query

$query1 = "INSERT INTO persons (name,age,address) 

values (?, ?, ?) ";

# prepare your statement for connecting to the database

$statement1 = $myConnection->prepare($query1);

printf("\n");

printf("\t\t===== ADD RECORD =====");

printf("\n\n");

print "\tEnter Persons Name    : ";

chomp($name =<>);

printf("\n");

print "\tEnter Persons Age      : ";

chomp($age =<>);

printf("\n");

print "\tEnter Persons Address  : ";

chomp($address =<>);

$name_saved = uc $name;

$address_saved = uc $address;

# execute your SQL statement

$statement1->execute($name_saved,$age,$address_saved);

printf("\n\n");

printf("\tRecord has been saved.");

printf("\n\n");

$statement1->finish();

}

elsif ( $option == 2 )

{

# update statement

$query2 = "UPDATE persons SET 

                 name =?,

                 age =? ,

                 address=?  

                 WHERE id = ?";

# prepare your statement for connecting to the database

$statement2 = $myConnection->prepare($query2);

printf("\n");

printf("\t\t===== UPDATE RECORD =====");

printf("\n\n");

print "\tEnter Persons ID    : ";

chomp($id =<>);

printf("\n");

print "\tEnter Persons Name    : ";

chomp($name =<>);

printf("\n");

print "\tEnter Persons Age      : ";

chomp($age =<>);

printf("\n");

print "\tEnter Persons Address  : ";

chomp($address =<>);

$name_saved = uc $name;

$address_saved = uc $address;

# execute your SQL statement

$statement2->execute($name_saved,$age,$address_saved,$id);

printf("\n\n");

printf("\tRecords has been updated successfully.");

printf("\n\n");

}

elsif ( $option == 3 )

{

# set the value of your SQL query

$query3 = "DELETE FROM persons WHERE id = ? ";

# prepare your statement for connecting to the database

$statement3 = $myConnection->prepare($query3);

printf("\n");

printf("\t\t===== DELETE RECORD =====");

printf("\n\n");

print "\tEnter Persons ID    : ";

chomp($id =<>);

printf("\n\n");

$statement3->execute($id);

printf("\tRecord deleted successfully");

}

elsif ( $option == 4 )

{

$query4 = $myConnection->prepare('SELECT * FROM persons');

$result4 = $query4->execute();

printf("\n");

printf("\t\t===== VIEW ALL RECORD =====");

# retrieve the records from the table Persons

printf("\n\n");

while(  my $ref = $query4->fetchrow_hashref() ) {

printf("\tID       :  $ref->{'id'} \n");

        printf("\tNAME     :  $ref->{'name'} \n");

        printf("\tAGE      :  $ref->{'age'} \n");

printf("\tADDRESS  :  $ref->{'address'} \n\n");

    }

  }  

elsif ( $option == 5 )

{

printf("\n\n");

printf("\tExiting the program. Thank you for using this Program.\n");

last;

}

else

{

print "Invalid Option\n. Try Again Please.";

}

}

persons.sql


-- phpMyAdmin SQL Dump
-- version 4.0.4.1
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Apr 21, 2020 at 07:05 AM
-- Server version: 5.6.11
-- PHP Version: 5.5.1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `records`
--

-- --------------------------------------------------------

--
-- Table structure for table `persons`
--

CREATE TABLE IF NOT EXISTS `persons` (
  `id` int(15) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `age` int(3) NOT NULL,
  `address` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

--
-- Dumping data for table `persons`
--

INSERT INTO `persons` (`id`, `name`, `age`, `address`) VALUES
(2, 'Julianna Rae Pomperada', 6, 'Eroreco, Bacolod City'),
(3, 'Jacob Samuel Pomperada', 7, 'Eroreco Subd.,Bacolod City'),
(7, 'BILL GATES', 65, 'ONE MICROSOFT WAY, WASHINGTON USA'),
(8, 'JAKE POMPERADA', 41, 'PUROK PAG-ASA,BARANGAY ALIJIS 6100 BACOLOD CITY, NEGROS OCCIDENTAL'),
(9, 'MA. JUNALLIE POMPERADA', 48, '#25-31 VICTORIA MALAGA STREET, ERORECO SUBD. BRGY. MANDALAGAN BACOLOD CITY,NEGROS OCCIDENTAL');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;