#!/usr/bin/perl
use strict; use warnings;
use CGI qw/:all/;
use CGI::Pretty;
use CGI::Carp "fatalsToBrowser";
use DBI;
my $serverName=;
my $serverPort=;
my $serverUser=;
my $serverPass=;
my $serverDb=;
my $serverTabl=;

if(!param()){
  htmlForm();
}else{
  my $error=insert_entry();
  show_entries($error);
}

sub htmlForm{
  print header();
  print start_html(-title=>'Lab 6',-scrip=>{-language=>'PerlScript', src=>'mySqlProg.cgi'});
  print h1("Enter data in to mysql table(lab 6)");
  print startform({-action=>"",-method=>"POST"}); 
  print table(TR(td("First Name:"),td(textfield({-name=>"fname",-size=>"11",-maxlength=>"10"}))),
              TR(td("Last Name:"),td(textfield({-name=>"lname",-size=>"11",-maxlength=>"10"}))),
              TR(td("Student ID:"),td(textfield({-name=>"id",-size=>"10",-maxlength=>"9"}))),
              TR(td("Logins:"),td(textfield({-name=>"logins",-size=>"4",-maxlength=>"3"}))));
  print submit(-name=>'submit_form', -value=>'Submit form');
  print end_form();
  print "<button type='button' value='view' onclick='show_entries();'>Show Table Values</button>";
  print "<button type='button' value='increment' onclick='increment();'>Increment logins by 1</button>";
  print "<button type='button' value='change' onclick='change();'>Any field, change the # 6 to 4</button>";
  print end_html();
}

sub insert_entry {
    my ($dbh,$sth,$Fname,$Lname,$studentID,$Logins,$success);                      
    $dbh=DBI->connect("DBI:mysql:database=$serverDb;host=$serverName;port=$serverPort",$serverUser,$serverPass)
        or die ("Cant connect to the database: ". DBI->errstr);
    $Fname=param("fname");
    $Lname=param("lname");
    $studentID=param("id");
    $Logins=param("logins");
    $success=$dbh->do("INSERT INTO $serverTabl(firstname,lastname,SID,logins) VALUES(?,?,?,?)", 
             undef,$Fname,$Lname,$studentID,$Logins);    
    $dbh->disconnect;
    if($success != 1){return "Sorry, the database was unable to add your entry.";}
}

sub show_entries {
    my($dbh, $sth, @row);
    $dbh=DBI->connect("DBI:mysql:database=$serverDb;host=$serverName;port=$serverPort",$serverUser,$serverPass);
    $sth=$dbh->prepare("SELECT firstname,lastname,SID,logins FROM $serverTabl");
    $sth->execute;
    print header();
    print start_html(-title=>'Lab 6');
    if($_){print "$_";}
    print "Existing Entries",hr;
    while(@row=$sth->fetchrow_array){
          print "First Name: ", $row[0], br;
          print "Last Name: ", $row[1], br;
          print "Student ID: ", $row[2], br;
          print "Logins: ", $row[3], br,br;
    }
    print end_html();
    $sth->finish;
    $dbh->disconnect;
}

sub increment{
   my($dbh);
   $dbh=DBI->connect("DBI:mysql:database=$serverDb;host=$serverName;port=$serverPort",$serverUser,$serverPass);
   $dbh->do("UPDATE $serverTabl SET logins=logins+1");
   $dbh->disconnect;
}

sub change{
   my($dbh, $sth, @row);
   $dbh=DBI->connect("DBI:mysql:database=$serverDb;host=$serverName;port=$serverPort",$serverUser,$serverPass);
   $sth=$dbh->prepare("SELECT firstname,lastname,SID,logins FROM $serverTabl");
   while(@row=$sth->fetchrow_array){
      for(my $i=0;$i<=3;$i++){
        if($row[$i]){
          $row[$i]=~s/6/4/g;
        }
      }
      $dbh->do("UPDATE $serverTabl SET firstname=$row[0],lastname=$row[1],SID=$row[2],logins=$row[3]
               WHERE firstame=$row[0]");
   }
   $sth->finish;
   $dbh->disconnect; 
}