RE: arbitrary number of bindings & LIKE statementThanks to everyone who replied. I'm
now able to do what I wanted using the "map" command, which I was not aware of before.
And Michael's method of preparing the sql as well as executing it was also a
lesson... thanks.
Just to explain what I was doing, since the way I phrased my question may have been
confusing, in case anybody wants to know:
I was just trying to build a CGI search engine that allows user to enter multiple
names in a form. Each of these names is searched for separately in the database so I
split the input into an array. The SQL statement was prepared according to the number
of elements in the array, so that was ok. The number of placeholders is "arbitrary"
because a user could enter as many names as desired. I was attempting to use
bind_param statement in order to associate each array_element/name with a
placeholder... and as I wrote, it was not working in a foreach (@names) context. I
thought this was sort of strange since the two methods:
$sth->bind_param(1, "%$names[0]%");
$sth->bind_param(2, "%$names[1]%");
$sth->execute || die "error couldn't execute";
-and-
my $sth = $dbh->prepare($sql) || die "couldn't prepate";
foreach $x (0..$#names) {
$sth->bind_param($x+1, "%$names[$x]%");
}
$sth->execute || die "error couldn't execute";
seem like they should accomplish the same thing, at least to me. But I guess DBI
handles it differently. I think that's interesting, but what do I know? (I'm self
taught with no previous computer experience... can't believe I got this far) In the
second case, only search results for the very last user entry would be returned, so if
the user listed five names only the data associated with the fifth name was returned.
Yet no error as far as incorrect number of passed parameters to placeholders would
take place.
Well, I don't know what what was going on, but the map syntax is great and it's fine
now. I just posted this explanation here to answer some of the questions about my
problem. Thanks again to everyone.
--Tony
----- Original Message -----
From: Wilson, Doug
To: 'Tony Vassilev' ; [EMAIL PROTECTED]
Sent: Wednesday, January 16, 2002 2:09 PM
Subject: RE: arbitrary number of bindings & LIKE statement
> From: Tony Vassilev [mailto:[EMAIL PROTECTED]]
> my $sth = $dbh->prepare($sql) || die "couldn't prepate";
What's the sql look like?
What results do you expect/get?
> $sth->bind_param(1, "%$names[0]%");
> $sth->bind_param(2, "%$names[1]%");
> $sth->execute() || die "error couldn't execute";
>
> That method of course requires that I know the number of
> bindings ocurring.
> For an arbitrary number, I've been trying this:
> $sth->execute || die "error couldn't execute";
Why call bind param so many times? just do:
$sth->execute(map "%$_%", @names) || die "...";
HTH,
Douglas Wilson
