SPUG: sql select on the fly

[Chris Sutton]

push @array, "filename='$file'" if ($file);
push @array, "author='$author'" if ($author);
push @array, "date='$date'" if ($date);

# Just join to add the "and" between elements
# that way if there is only one element, you don't get
# any ands, you just get the one where clause
$where = join " and ", @array;

# Make sure something is in where
if ($where) {
   $select_query = "select * from table where $where";
} else {
   print "No where information";
}

You also want to make sure and dbi->quote the information going into the
where, as something like Author="o'dea" will turn into author='o'dea'
which will hoze everything.

On Thu, 21 Jun 2001, 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/