Archive for category perl hacks

Posted via Perl

Posted via Perl

INSERT DELETE AND MODIFY SEARCH DATABASE BASED ON FILTERS

#!/usr/bin/perl

use DBI;
use DBD::mysql;

# CONFIG VARIABLES
$platform = “mysql”;

$database = “<databasename>”;

$host = “localhost”;
$port = “3306?;
$tablename = “<tablename>”;
$user = “<username>”;
$pw = “<password>”;
$i=0;

$j=1;

# DATA SOURCE 
$dsn = “dbi:mysql:$database:$host:$port”;

# PERL DBI CONNECT
$connect = DBI->connect($dsn, $user, $pw);

# IF THERE IS AN ERROR CONNECTING TO THE DATABASE 
if (not $connect) {

print “The connection attempt failed for the following reason:$DBI::errstr”;
}

sub display
{
# TAKING THE FILTER TO DISPLAY THE FIELDS
local $j=0;
$filter = $_[1];
$coltodisp = $_[0];

# PREPARE THE QUERY

$query = “SELECT * FROM $tablename where $coltodisp=”$filter”";
# $query = “show tables”;
$query_handle = $connect->prepare($query);

# EXECUTE THE QUERYS
$query_handle->execute();



# LOOP THROUGH 
while (@row= $query_handle->fetchrow_array()) {

$i = 0;
print “nn”;
print “***********************************************************************************************************” . “n”;
print “ $j”;
$j += 1;
print “n***********************************************************************************************************” . “n”;
for $tables (@row) {

print “n—————–” . “n”;
print “$colo[$i]” . “n”;
print “——————-” . “n”;
print “$tables” . “n”;
$i += 1;
}
}
$j -= 1;
print “n$j matchings foundn”;

$rc = $query_handle->finish;
}
sub dbsearch {
$coltosearch = $_[0];
$pattern = $_[1];
$query = “select * from $tablename where $coltosearch like “%$pattern%”";
$query_handle = $connect->prepare($query);
$query_handle->execute();

while (@row= $query_handle->fetchrow_array()) {
$i = 0;
print “nn”;
print “***********************************************************************************************************” . “n”;
print “PATTERN MATCHING $j”;
$j += 1;
print “n***********************************************************************************************************” . “n”;
for $tables (@row) {

print “n—————–” . “n”;
print “$colo[$i]” . “n”;
print “——————-” . “n”;
print “$tables” . “n”;
$i += 1;
}
}
$j -= 1;
print “n$j matchings foundn”;

$rc = $query_handle->finish;
}

# TAKING TABLES INFORMATION IN TO ARRAY
$query1 = “DESCRIBE $tablename”;
$query1_handle = $connect->prepare($query1);
$query1_handle->execute();
print “*********************************************************************************************************n”;

print “Coloums information in the table” . “n”;
print “*********************************************************************************************************n”;
while(@coloum= $query1_handle->fetchrow_array())  {
$colo[$i] = $coloum[0];
print “$i.  “;
print “$coloum[0]” . “n”;

$i += 1;
}
print “*********************************************************************************************************nn”;

while(1)   {
# TAKING INPUT FROM USER TO INSERT,DELETE,EDIT,SEARCH
print <<EOP;
Enter some :

1) 1 for insert
2) 2 for delete
3) 3 for edit
4) 4 for display
5) 5 for search
EOP
print “nEnter some number to carryout the task ::”;
$num = <>;

if ($num == 1)  {
$i = 0;
$query = “INSERT INTO $tablename (”;
for $val (@colo) {
print “Enter $val to insert :: “;
$toinsert[$i] = <>;
chomp($toinsert[$i]);

$i += 1;
$query = $query . “$val, “;
}
$query = $query . “) VALUES (”;
for $val1 (@toinsert)  {
$query = $query . “‘$val1?, “;
}
$query = $query . “)”;

print “n”;
$query =~ s/,s+)/)/g;
$query_handle = $connect->prepare($query);
$query_handle->execute();
}

elsif ($num == 2)  {
print “Follow the  to delete a particular row”;

print “ENter the coloum name to filter table display:: “;
$coltodisp = <>;
chomp($coltodisp);
print “Enter $coltodisp :: “;
$temp = <>;
chomp($temp);
$arbit = &display($coltodisp,$temp);

print “Do you want to delete the entry (y/n)?”;
$ans = <>;
chomp($ans);
if($ans eq “y” || $ans eq “yes”)
{
$query = “DELETE from $tablename where $coltodisp=”$temp”";
$query_handle = $connect->prepare($query);
$query_handle->execute();

}
}

elsif($num == 3)  {
print “ENter the coloum name to filter table display:: “;
$coltodisp = <>;
chomp($coltodisp);
print “Enter $coltodisp :: “;

$temp = <>;
chomp($temp);
print “Enter the coloum you want to edit:: “;
$coltoedit = <>;
chomp($coltoedit);
$arbit = &display($coltodisp,$temp);
print “Enter the value to edit:: “;

$edittoval = <>;
chomp($edittoval);
print “Do you really want to edit the field entry (y/n)?”;
$ans = <>;
chomp($ans);
if($ans eq “y” || $ans eq “yes”)
{
print “:”;

$query = “UPDATE $tablename SET $coltoedit=’$edittoval’ WHERE $coltodisp=”$temp”";
$query_handle = $connect->prepare($query);
$query_handle->execute();
}
}

elsif($num == 4)  {
print “Enter the Coloum name to filter table display:: “;

$coltodisp = <>;
chomp($coltodisp);
print “Enter $coltodisp :: “;
$temp = <>;
chomp($temp);
$arbit = &display($coltodisp,$temp);
}

elsif($num == 5)  {
print “Enter the coloum name to search for a pattern:: “;
$coltosearch = <>;
chomp($coltosearch);
print “Enter the pattern to be searched:: “;
$pattern = <>;
chomp($pattern);

&dbsearch($coltosearch,$pattern);
}

}