Re: 3 strings to join

What I often do:


my $sql="$a where ";
my @where;
push @where, $b if $b;
push @where, $c if $c;

$sql .= join " and ", @where;
[download]

Usually the push'es are in a block where the variables are being created, so I don't need the 'if $b' or 'if $c' clauses.
This also lets me create an @args array so that I can use placeholders in the where clauses and use the array of arguments during the execute (assuming that you're using DBI). Eg:

my $sql="$a where ";
my (@where, @args);

if ($something) {
 push @where, "field1 = ?";
 push @args, $some_value; 
}
if ($something_else) {
 push @where, "field2 = ?";
 push @args, $another_value;
}
$sql .= join " and ", @where;
# Assume RaiseError is true;
# If this is executed more than once
# in this script,
# use 'prepare_cached' instead.
my $sth = $dbh->prepare($sql);
$sth->execute(@args);
[download]

Some of the many benefits of placeholders are that you don't have to escape quotes (or generally any other character), you can prepare the statement once and execute it many times with different arguments, and even if you DO only execute it once in this script, some databases can cache the statement and so using placeholders can let the database utilize the cache more efficiently over multiple executions with different arguments.

Comment on Re: 3 strings to join Select or Download Code

Replies are listed 'Best First'.
RE: Re: 3 strings to join by jptxs (Curate) on Sep 29, 2000 at 02:27 UTC
update: actually i guess Ovid's also assumes a where clause, but then again so did my original post :) but it can be easily adapted by simply using saying `else { $string = $a }` at the end... I do something very similar alot, but the problem is that this assumes there will be a where clause. I'm in a situation where there could be none or many. It's also not a whole list of things, just a few. I think Ovid's response is what I was looking for, but you have done this a bit nicer than i usually do so i'll take that too :) -- I'm a solipsist, and so is everyone else. (think about it)	[reply] [d/l]
RE: RE: Re: 3 strings to join by runrig (Abbot) on Sep 29, 2000 at 02:34 UTC
If there a possibility of no where clause, then it becomes: `my $sql = $a; ... $sql .= " where ".join(" and ", @where) if @where;` [download] And as for placeholders, the @args array will be empty, so there's no harm in including it in the execute.	[reply] [d/l]