my $pg = param("pg");

my $message = param("pg");

my $sql = "SELECT subscr_id,username FROM members WHERE subscr_id = ?";

$sth->execute($id);

# retrieve user from the db 
my $username = get_user($id); 
 
if ( $username ) { 
    .... 
    .... 
    .... 
} 
 
 
sub get_user { 
 
    my $id        = shift; 
    my $host      = "remotehost"; 
    my $database  = "dbase"; 
    my $tablename = "tname"; 
    my $user      = "user"; 
    my $pw        = "password"; 
 
    my $dbh = DBI->connect("DBI:mysql:$database:$host" , $user, $pw, 
			   { RaiseError => 1} ) 
            or die "Connection Error: $DBI::errstr\n"; 
 
  # no need to select subscr_id since we already know that value 
  # and will be using it to retrieve the "username", if the id is found 
    my $sql = "SELECT username FROM members WHERE subscr_id = ?"; 
    my $sth = $dbh->prepare($sql); 
    $sth->execute($id); 
    my $username = $sth->fetchrow_array; 
 
    $sth->finish(); 
    $dbh->disconnect; 
     
    return $username;  # returns the 'username' or undef if not found in the db 
}

#!/usr/bin/perl 
use warnings FATAL => 'all'; 
use strict; 
use diagnostics; 
use CGI qw/param/; 
use POSIX qw/strftime/; 
use DBI; 
use CGI::Carp qw(fatalsToBrowser);  # remove this when the script is complete and put into production 
 
print "ContentType: text/html\n\n"; 
 
my $host = "remotehost"; 
my $database = "dbase"; 
my $tablename = "tname"; 
my $user = "user"; 
my $pw = "password"; 
 
# Obtain items from web page. 
my $id = param("id"); 
my $ip = param("ip"); 
my $pg = param("pg"); 
## End items from web page 
 
my $phone; 
my $file = "_iptrace.txt"; 
my $filea = "/tmp/"; 
my $filec = ".txt"; 
my $filed = "/tmp/invalid_id_iptrace.txt"; 
 
# message 
# 1 = email 
# 2 = phone 
# 3 = both 
my $message = param("pg"); 
 
my $dbh = DBI->connect("DBI:mysql:$database:$host" , $user, $pw)   
     or die "Connection Error: $DBI::errstr\n"; 
 
my $sql = "SELECT subscr_id,username FROM members WHERE subscr_id = ?"; 
my $sth = $dbh->prepare($sql); 
 
$sth->execute($id); 
 
my ($subscr_id, $username) = $sth->fetchrow_array; 
 
#$sth = $dbh->finish(); 
 
    if ($id eq '$subscr_id') 
    { 
	#if we have a match open the file to write. 
	open(FILE, '>'."$filea$subscr_id$file"); 
	 
	# lets put the time in the file. 
	print FILE strftime("%A %B %d, %Y - %I:%M %p %Z\n", localtime(time) ); 
 
	# put the IP address and web page visited. 
	print FILE " -- IP Address: $ENV{REMOTE_ADDR}\n\t Accessed your page: $ENV{HTTP_REFERER}\n------\n"; 
	print FILE " -- Here is the information we found:\n------\n"; 
 
	#close the file. Finished with the first part 
	close(FILE); 
 
	# Run the IP2Location script. 
	system("./iptrace.sh $ENV{REMOTE_ADDR} json city>>/tmp/$subscr_id$file"); 
 
	# Lets send an email and or text with all the information. 
	# Send email 
	 
	if($message == 1) 
	{  
	    system("mail -s 'Web Page visited' $username \< /tmp/$subscr_id$file"); 
	#  print "message 1"; <-- example  
	}  
	elsif($message == 2) 
	{ 
	    system("mail -s 'Web Page visited' $filea$subscr_id\.txt \< /tmp/$subscr_id$file"); 
	#    do something else 
	}  
	elsif($message == 3) 
	{ 
	    system("mail -s 'Web Page visited' $username \< /tmp/$subscr_id$file"); 
	    system("mail -s 'Web Page visited' $phone \< /tmp/$subscr_id$file"); 
	#    do something 
	} 
	else  
	{ 
	    ; 
	    # No conditions met.    
	} 
	print "ContentType: text/html\n\n"; 
	print "<!--\n"; 
	print "var x;\n"; 
	print "//-->\n"; 
	exit(); 
    } 
    else 
	{ 
	#  "To Do" send a page or popup if the id is not valid. 
	    $filed = "/tmp/invalid_id_iptrace.txt"; 
	    open (FILED, '>'."$filed") or die $!; 
	    print FILED strftime("%A %B %d, %Y - %I:%M %p %Z\n", localtime(time) ); 
	    print FILED " -- IP Address: $ip\n\t Access Page: $ENV{HTTP_REFERER} \n From id: $id with :pg as $pg\n------\n"; 
#	    print FILED " -- Database ID: \n------\n"; 
	    close(FILED); 
	    system("mail -s 'Page visited Unknown' admin\@domain.com \< $filed"); 
	    print "ContentType: text/html\n\n"; 
	    print "<!--\n"; 
	    print "var x;\n"; 
	    print "//-->\n"; 
	    exit(); 
	} 
exit(); 
#end of script

if ($id eq '$subscr_id')

my $iptrace = "_iptrace.txt"; 
my $invalid_iptrace = "/tmp/invalid_id_iptrace.txt"; 
my $dir  = "/tmp"; 
my $ext  = ".txt";

use Data::Dumper; 
 
print Dumper $id, $subscr_id;