#!/usr/bin/perl
#
# A simple program to perform simple operations on a mysql database.
#
# alotlikeyesterday@gmail.org
#

use strict;
use DBI;

#Database connection details
my $driver = "mysql";
my $server = "";
my $user = "";
my $password = "";
my $database = "test";
my $table = "foo";
my $url = "DBI:$driver:$database:$server";

#Connect to the database
my $dbh = DBI->connect( $url, $user, $password ) 
  or die "-->Could not connect to the database\n";
print "\nConnected to the $database database\n";

#Main loop
my $dc = "n";
while ( $dc == "n" ) {
  print "What would you like to do? 
      [r]eturn all rows [a]dd record [d]elete record [q]uit >> ";

  $dc = <>;
  chomp($dc);

  if ($dc =~ 'q' ) { &quit; }
  if ($dc =~ 'r' ) { &return_rows; }
  if ($dc =~ 'a' ) { &add_data; }
  if ($dc =~ 'd' ) { &delete_data; }

}						#end while

#Sub routines
sub return_rows {
	print "\n";
	my $sth = $dbh->prepare("SELECT * FROM $table");
	if (!$sth) {
	    die "Error:" . $dbh->errstr . "\n";
	}
	if (!$sth->execute) {
	    die "Error:" . $sth->errstr . "\n";
	}
	my $names = $sth->{'NAME'};
	my $numFields = $sth->{'NUM_OF_FIELDS'};
	my $count = 0;

	for (my $i = 0;  $i < $numFields;  $i++) {
	    printf("%s%s", $i ? "," : "", $$names[$i]);
	}
	print "\n";
	print "----------\n";
	while (my $ref = $sth->fetchrow_arrayref) {
	    for (my $i = 0;  $i < $numFields;  $i++) {
	        printf("%s%s", $i ? "," : "", $$ref[$i]);
	print $i;
		$count++;
	    }
	    print "\n";
 	}
       $sth->finish();
       print "\n";

	print "There are $count/$numFields rows in $table\n\n";
}

sub delete_data {
        print "  Data to be deleted: [Column name, value (no spaces)]";
        my $data = ;
        chomp($data);
        my @data2 = split(/ /, $data);
         foreach my $seg (@data2) {
           my $data3 = $seg;
          print "Input: $data3\n";
        }
         print "  Is this correct? [y,n] ";
         my $response = ;
         chomp($response);
          if ($response =~ 'y' ) {
           $dbh->do("DELETE FROM $table WHERE @data2[0] = '@data2[1]'");
           print "\n";
          }
}

sub add_data {
	print "  Enter the ID: ";
	my $data1 = ;
	chomp($data1);
	print "  Enter the data: ";
	my $data2 = ;
	chomp($data2);
	print "\n";
	print "  Input ID: $data1\n";
	print "  Input Data: $data2\n";
        print "  Is this correct? [y,n] ";
        my $response = ;
        chomp($response);
        if ($response =~ 'y' ) {
          $dbh->do("INSERT INTO $table VALUES (?,?)",undef, $data1, $data2);
          print "\n";
        }	
}

sub quit {
	print "Goodbye.\n"; 
	$dbh->disconnect();				#Disconnect from the DB
	exit 0; 
}