SPUG: sql select on the fly

C.J. Collier cjcollier at sinclair.net
Sat Jun 23 08:59:55 CDT 2001


Martin,

I always did something like the following:

#the Vars() function, which returns the params as a hash, is in cgi-lib
use CGI qw(:standard :cgi-lib);
...
my %params = Vars();
my $select = "SELECT ... ";
my $from = "FROM ... ";
# set up where statement with '?' as placeholder
my $where = "WHERE" . join(" AND ", map{ "$_ = ?" } sort keys %params) .
" ";
my $order = "ORDER BY ...";
my $query = $select . $from . $where . $order;
my $sth = $dbh->prepare($query);

# fill in the place-holders with the values of the params hash
$sth->execute(values(@params{ sort keys %params }));

EOF

But note that Vars() doesn't grok multi-valued params, like check boxes
or scrolling_list.

C.J.

Martin Korb wrote:

>  I have a question concerning creating a sql-select statement on the
> fly. The user has a choice of any combination of 4 fields (either one
> or all 4). What is an elegant way to create a sql - select query based
> on the fields the user fills out. my $file = param(Filename);my
> $author = param(Author);my $date = param(Date); my $select_query =
> "Select * from table where filename =\'$file\' and author
> =\'$author\'";  #####    I know I an do it in a round about way by
> using if ($file) {    push @array, "filename =\'$file\' and ";} and
> then do a join on the @array elements.  Are there better ways to
> handle this? Thanks Martin


 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     POST TO: spug-list at pm.org       PROBLEMS: owner-spug-list at pm.org
      Subscriptions; Email to majordomo at pm.org:  ACTION  LIST  EMAIL
  Replace ACTION by subscribe or unsubscribe, EMAIL by your Email-address
 For daily traffic, use spug-list for LIST ;  for weekly, spug-list-digest
  Seattle Perl Users Group (SPUG) Home Page: http://www.halcyon.com/spug/





More information about the spug-list mailing list