Get Data From SQL

modified from Chris Sung's code by Hsiao-Ho Hsu

This perl code reads from a mySQL database and returning the requested results. It's similar to this SQL reader by Keunyoung Oh

#!/usr/bin/perl

if    ($0=~m#^(.*)\\#){ $execDir = "$1"; }  # Win/DOS
elsif ($0=~m#^(.*)/# ){ $execDir = "$1"; }  # Unix
else  {`pwd` =~ /(.*)/; $execDir = "$1"; }  # Unix

# Get Login Info
require "$execDir/info.pl";

use CGI::Carp qw(fatalsToBrowser);
use DBI;
use Net::HTTP;


# Define the name of the script just for reference
$scriptName = "getData.pl";
$pageTitle = "what is this";

# Obtain any variables submitted either in the URL string
#  or by an HTML form via the GET or POST method, and
#  place in the %INPUT_VARS hash array
%INPUT_VARS = ();
&input_vars_receive;
&input_vars_parse;

$input = $INPUT_VARS{"i"};

#########################################################
# Define our DB info
#########################################################



$database = $mySqldatabase;
$data_source = "dbi:mysql:$database:$myhost";
$username = $mySqlUsername;
$password = $mySqlPassword;




#########################################################
# Connect to the database.
#########################################################

my $dbh = DBI->connect($data_source, $username, $password,
          {'RaiseError' => 1, 'PrintError' => 1});

print "Content-type: text/html\n\n";

$SqlStatement = "DELETE FROM dwd_temp WHERE temp='0' ";



if ($input eq "2")
    {	
    $SqlStatement = 
	# "SELECT temp FROM dwd_temp WHERE dateRecord>DATE_SUB(CURDATE(),INTERVAL 7 DAY) order by dateRecord"
	 "SELECT temp FROM   dwd_temp WHERE  (id ,0) IN (SELECT id, MOD(id, 7) FROM dwd_temp)AND dateRecord>DATE_SUB(CURDATE(),INTERVAL 7 DAY) order by dateRecord";
    # "DELETE temp FROM dwd_temp WHERE temp eq 0";

	}

	elsif ($input eq "3")
	{	
	$SqlStatement = 
	# "SELECT temp FROM dwd_temp WHERE dateRecord>DATE_SUB(CURDATE(),INTERVAL 1 MONTH) order by dateRecord";
     "SELECT temp FROM  dwd_temp WHERE  (id ,0) IN (SELECT id, MOD(id,14) FROM dwd_temp)AND dateRecord>DATE_SUB(CURDATE(),INTERVAL 14 DAY) order by dateRecord";
	 # "DELETE temp FROM dwd_temp WHERE temp eq 0";
	}
	elsif ($input eq "1")
	{	$SqlStatement = 
	#"SELECT temp FROM dwd_temp WHERE dateRecord>DATE_SUB(CURDATE(),INTERVAL 1 DAY ) order by dateRecord" ;
    "SELECT temp FROM  dwd_temp WHERE  (id ,0) IN (SELECT id, MOD(id,2) FROM dwd_temp)AND dateRecord>DATE_SUB(CURDATE(),INTERVAL 1 DAY) order by dateRecord";
	#  "DELETE temp FROM dwd_temp WHERE temp eq 0";
	}

$data = "";
$data_one = "";
$sth = $dbh->prepare($SqlStatement);
$sth->execute();

while (@row_array = $sth->fetchrow_array())
{	$temp  = $row_array[0];
    $another = $row_array[1];


	if ($data ne "")
	{	$data .= ","; }

	$data .= $temp;
}



#  $counter = 0;
#  @another = ();
#  foreach $temp (@row_array[0] ) {
#    if ($temp % 10) {
#        push(@another, $temp);
#    }
#    $counter++;
#    }	




#   if ($data_one ne "")
#	{	$data_one .= ",";
#	 }

#	$data_one .= $another;
###### with restrain data in ############

print $data ;
#print $data_one  \n;



#########################################################
# Disconnect from the database.
#########################################################   
$dbh->disconnect();


exit(0);


#########################################################
# Takes data from an HTML Form or URL string
#########################################################
sub input_vars_receive
{ $formData = $ENV{'QUERY_STRING'};

	if ($ENV{'REQUEST_METHOD'} eq 'POST')
	{	read(STDIN, $formData, $ENV{'CONTENT_LENGTH'});
	}
}

#########################################################
# Parses form info
#########################################################
sub input_vars_parse
{	local($name,$value,$pair);
	local(@pairs) = split(/&/, $formData);

	# Get parameter names, their values and copy into $INPUT_VARS array
	foreach $pair (@pairs)
	{	($name,$value)=split(/=/,$pair);
		$value=~tr/+/ /s;
		$value=~s/%([a-fA-F0-9][a-fA-F0-9])/pack("C",hex($1))/eg;
		$INPUT_VARS{$name}=$value;
	}
}

#########################################################
# Write To Log File
#########################################################
sub write_to_log
#########################################################
{ # Write to log file
	local($msg) = @_;
	open INPTR, ">>$logFile";
	print INPTR "$msg\n";
	close INPTR;
}

#########################################################
# Trim whitespace
#########################################################
sub trim_it
{ local($msg) = @_;
  while ($msg=~/^\s/i) { $msg=~s/^\s//i;}
  while ($msg =~/\s$/)  { $msg =~s/\s$//; }

	return $msg;
}

#########################################################
# Escape single quotes for DB entry
#########################################################
sub escape_quotes
{ local($msg) = @_;
	local($delim) = "'"; local($rep) = "''";

	$msg =~s/$delim/$rep/eg;

	return $msg;
}