Re: dynamically input fields to select from my table in the database

Aruna Goke Wed, 01 Oct 2008 03:02:33 -0700

Rob Dixon wrote:

Goke Aruna wrote:

Can someone be of help;


I have the code below, what i wanted this code to do is to dynamically
create fields to select and again to dynamically subtitute the value of the
given fields.

my sms content is the source of my data and my table definition is as below:

 (Id int auto_increment primary key,
 Name varchar(32),
 TradeName varchar(20),
 Address  varchar(50),
 City varchar(13)
 State varchar(10),
 phone varchar(30),
 IndType varchar(20)
 );

user can determine their known and unknown fields as they like.. I should be
able to know which fields to select and which fields is supplied by the
user.

Thanks

goksie


#!/usr/bin/perl
#
use warnings;
use strict;
use DBI;

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='test123';
 $port='3306';
my $name='localhost';
 $dsource="dbi:mysql:yellopgdb:$name";
  $dbh = DBI->connect( $dsource, $user, $pass )|| die ("Couldn't connect to
yellopgdb !\n");


#user supplies name unknown, address unknown,
#
#user send sms to myphonenumber 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 %sms =split(/,/, $smscontent);
my @smswanted =();
my @given =();
#
foreach(keys %sms){
push @smswanted, $_ if $sms{$_} eq 'query';
push @given, $_, $sms{$_} if $sms{$_} ne 'query';
}

=begin
$cname = $coy{'name'};
$ctradename = $coy{'tradename'};
$caddress = $coy{'address'};
$ccity = $coy{'city'};
$cstate = $coy{'state'};
$cphone = $coy{'phone'};
$cindtype = $coy{'indtype'};
=end
=cut

my $qry = "select $smswanted[0],$smswanted[1],$smswanted[2] from yellopg
where $given[0]=? and $given[2]= ?";
$sth = $dbh->prepare($qry);
$sth->execute($given[1], $given[3]);
my @userinfo = $sth->fetchrow_array;
print @userinfo;


What is your question?

The code you have written looks like it should work. Are you having problems
with it? Where do you want to go from here?

Rob

Thanks Rob,

from the query.. if i statically substituted the array elements on bothselect and the condition sides, it worked. however, I want to give roomto "select @smswanted(ie. only requested columns) and the condition toowill will be where $given[0]...[6] will be dynamically substituted toprepare the dbh.

However, I have managed myself out of the select side. what isoutstanding is on the conditional side in which where clause needsfields 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 queriesto send sms back to my users.


This is actually working but i want to reduce the code length.


goksie


#!/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);
while (defined($_=$file->read)){
        @sms = split/\[/;
        next unless ($sms[6]=~/to:$simcardno/);
        $sms[6]=~/to:$simcardno/;
        $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 tableyellopgdb.yellopg

 $pass='itsme';
 $port='3306';
my $name='localhost';
 $dsource="dbi:mysql:yellopgdb:$name";

$dbh = DBI->connect( $dsource, $user, $pass )|| die ("Couldn'tconnect to yellopgdb !\n");


my $myphone = '2348099556636';

###########################################
#user supplies name unknown, address unknown,

#user send sms to $simcardno with content tradename,onet city,ibadanname,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){

my $qry = "select @smswanted[0..$#smswanted] from yellopg where$given[0]=? ?";

        $sth = $dbh->prepare($qry);
        $sth->execute($given[1]);
        @userinfo = $sth->fetchrow_array;