Aruna Goke wrote:
Thanks Rob,
from the query.. if i statically substituted the array elements on
both select and the condition sides, it worked. however, I want to
give room to "select @smswanted(ie. only requested columns) and the
condition too will will be where $given[0]...[6] will be dynamically
substituted to prepare the dbh.
However, I have managed myself out of the select side. what is
outstanding is on the conditional side in which where clause needs
fields and corresponding supplied values. The new code is as below ..
but i want to shorten it in such a way that I run two or three queries
to send sms back to my users.
This is actually working but i want to reduce the code length.
I tried to compile this but there were too many errors and warnings. So
how is this actually "working"?
#!/usr/bin/perl
use strict;
use warnings;
use File::Tail;
use diagnostics;
use DBI;
my $name = "/var/log/yellowpgs/yellowpgs_access.log";
my $simcardno='23480999380922';
###sample log messages
########################
#### 2008-06-26 13:19:25 Receive SMS [SMSC:caxton] [SVC:] [ACT:]
[BINF:] [from:+23422019119] [to:+2349191]
### [flags:-1:0:-1:0:-1]
[msg:17:tradename,onet,city,ibadan,name,query,phone,query,indtype,query]
[udh:0:]
########################
open my $file, '<', $name or die "could not open $name: $!";
$file=File::Tail->new(name=>$name, maxinterval=>3, adjustafter=>5);
Why are you opening the file with open() and then opening the file again
with File::Tail?
while (defined($_=$file->read)){
@sms = split/\[/;
next unless ($sms[6]=~/to:$simcardno/);
$sms[6]=~/to:$simcardno/;
Why do you test this again, and in void context?
$sender = $sms[5];
$sender =~ s/from:\+(\d+)\]\s+/$1/;
$msg = $sms[8];
$msg =~ s/(\S+)]$/$1/;
# $msg =~ s/msg:\d+?:(\S+)$/$1/;
@msg = split/:/, $msg;
#msg = (split/:/, $msg);
# I need only sender and $msg
}
########################################
## connect to my database for querying and sms response
########################################
my ($dsource, $user, $pass, $ret, $sql, $dbh, $sth, $row, $port,
$hostname, $database, $data);
$user='test'; ## user sade need select only access to the table
yellopgdb.yellopg
$pass='itsme';
$port='3306';
my $name='localhost';
"my" variable $name masks earlier declaration in same scope at
database.pl line 155.
$dsource="dbi:mysql:yellopgdb:$name";
$dbh = DBI->connect( $dsource, $user, $pass )|| die ("Couldn't
connect to yellopgdb !\n");
Because '=' has lower precedence than '||' you need to either enclose
the assignment in parentheses or use the lower precedence 'or' operator.
my $myphone = '2348099556636';
###########################################
#user supplies name unknown, address unknown,
#user send sms to $simcardno with content tradename,onet city,ibadan
name,query phone,query indtype,query
## my $smscontent =
"tradename,onet,city,ibadan,name,query,phone,query,indtype,query";
###########################################
my $smscontent = $msg[2];
my %sms =split(/,/, $smscontent);
my @smswanted =();
my @given =();
#
foreach(keys %sms){
push @smswanted, $_ if $sms{$_} eq 'query';
push @cntsmswant, $_ if $sms{$_} eq 'num';
push @namewanted, $_ if $sms{$_} eq 'all';
push @given, $_, $sms{$_} if $sms{$_} ne 'query';
}
$givenfield = scalar(@given);
@smswanted = join",", @smswanted;
if($givenfield=2){
You are using assignment instead of the numeric equality test so
$givenfield will always be equal to 2.
my $qry = "select @smswanted[0..$#smswanted] from yellopg
where $given[0]=? ?";
$sth = $dbh->prepare($qry);
$sth->execute($given[1]);
@userinfo = $sth->fetchrow_array;
system("wget",
"http://localhost:13013/cgi-bin/sendsms?username=mailsms&password=asyouwantit&dlr-mask=31&[EMAIL PROTECTED]&time=$now_string&sender=Helpdesk&to=$sender");
}elsif($givenfield=4){
You are using assignment instead of the numeric equality test but since
you already assigned 2 to $givenfield you will never get to this point.
if(scalar(@smswanted) > 0){
my $qry = "select @smswanted[0..$#smswanted] from yellopg
where $given[0]=? and $given[2]= ?";
$sth = $dbh->prepare($qry);
$sth->execute($given[1], $given[3]);
@userinfo = $sth->fetchrow_array;
system("wget",
"http://localhost:13013/cgi-bin/sendsms?username=mailsms&password=asyouwantit&dlr-mask=31&[EMAIL PROTECTED]&time=$now_string&sender=Helpdesk&to=$sender");
}elsif(scalar(@cntsmswant>0){
You are missing a closing parenthesis.
my $qry = "select count(*) from yellopg where $given[0]=? and
$given[2]= ?";
$sth = $dbh->prepare($qry);
$sth->execute($given[1], $given[3]);
@userinfo = $sth->fetchrow_array;
system("wget",
"http://localhost:13013/cgi-bin/sendsms?username=mailsms&password=asyouwantit&dlr-mask=31&[EMAIL PROTECTED]&time=$now_string&sender=Helpdesk&to=$sender");
}else{
my $qry = "select name from yellopg where $given[0]=? and
$given[2]= ?";
$sth = $dbh->prepare($qry);
$sth->execute($given[1], $given[3]);
@userinfo = $sth->fetchrow_array;
system("wget",
"http://localhost:13013/cgi-bin/sendsms?username=mailsms&password=asyouwantit&dlr-mask=31&[EMAIL PROTECTED]&time=$now_string&sender=Helpdesk&to=$sender");
}
}elsif($givenfield=6){
You are using assignment instead of the numeric equality test but since
you already assigned 2 to $givenfield you will never get to this point.
my $qry = "select @smswanted[0..$#smswanted] from yellopg
where $given[0]=?, $given[2]= ? and $given[4]= ?";
$sth = $dbh->prepare($qry);
$sth->execute($given[1], $given[3],$given[5]);
@userinfo = $sth->fetchrow_array;
system("wget",
"http://localhost:13013/cgi-bin/sendsms?username=mailsms&password=asyouwantit&dlr-mask=31&[EMAIL PROTECTED]&time=$now_string&sender=Helpdesk&to=$sender");
}else{
$userhelp = "send sms to $myphone eg.
tradename,onet,city,ibadan,name,query,phone,query,indtype,query";
system("wget",
"http://localhost:13013/cgi-bin/sendsms?username=mailsms&password=asyouwantit&dlr-mask=31&text=$userhelp&time=$now_string&sender=Helpdesk&to=$sender";);
}
John
