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