SPUG: sql select on the fly

[jeff]

On Sat, 23 Jun 2001, C.J. Collier wrote:

> 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) .
> " ";


You should probably add  ' grep { !/cgi|params|you|want|to|ignore/ } '
between sort and map. 


> 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.


Vars() returns a string with "\0" between each value in a multi value
param. You can separate them by:

my @values = split "\0", $param;


Jeff


> 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/
> 
> 
> 


 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     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/