#!perl
use strict;
use warnings;
use DBI;
my $DBH = get_dbh(); # connect to db
#$DBH->do('DELETE FROM tbl1');

# insert records (ID_NUMBER,PHONE) into tbl1
# Tests
print insert(1,'00001');
print insert(2,'00002');
print insert(2,'00002');
print insert(2,'00003');
print insert(3,'00002');
print insert(2,undef);
print insert(3,undef);
print insert(undef,'00002');
print insert(undef,'00003');
print insert(undef,undef);

sub insert {
  my ($ID_NUMBER,$PHONE) = @_;
  my @error = ();
  if ($PHONE) { 
    my $SQL = 'SELECT COUNT(*) FROM tbl1 
               WHERE PHONE = ?';                              
    my ($count) = $DBH->selectrow_array($SQL,undef,$PHONE);
    push @error,"PHONE exists ($PHONE)" if $count;
  } else {
    push @error,"PHONE blank"; 
  }  

  if ($ID_NUMBER){
    my $SQL = 'SELECT COUNT(*) FROM tbl1 
               WHERE ID_NUMBER = ?';                              
    my ($count) = $DBH->selectrow_array($SQL,undef,$ID_NUMBER);
    push @error,"ID_NUMBER exists ($ID_NUMBER)" if $count; 
  } else {  
    push @error,"ID_NUMBER blank"; 
  }
  
  if (@error){ 
    return "Errors : @error\n"
  } else {
    my $SQL = 'INSERT INTO tbl1(PHONE, ID_NUMBER)
               VALUES(?,?)';
    my $count = $DBH->do($SQL,undef,$PHONE, $ID_NUMBER);
    return "$count record inserted ($PHONE,$ID_NUMBER)\n";
  }
}

sub get_dbh{
  my $database = "test"; my $user = ""; my $pw = "";
  my $dsn = "dbi:mysql:$database:localhost:3306"; 
  my $dbh = DBI->connect($dsn, $user, $pw, 
     { RaiseError=>1, AutoCommit=>1 } );
  return $dbh;
}