Something very similar came up a couple days ago. See the attached email.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
----- Original Message -----
From: "Moritz von Schweinitz" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, January 18, 2002 14:35
Subject: inserting CGI-data into mysql-DB style question
> sorry if this is a banal question, but my brain just got sick and tired
> of
>
> $dbh->do("INSERT INTO tablename
> (
> firstname,
> surname,
> bla3,
> bla4,
> bla5,
> bla6
> )
> VALUES
> (
> ?,
> ?,
> ?,
> ?,
> ?,
> ?
> )
> ", undef, $in{firstname}, $in{surname}, $in{bla3}, [and
> so on]);
>
> is there a nice way to qoute the contents of my %in, and insert them
> without always having to count question-marks, and watching out that the
> right hash-element maps to the right question-mark, which in turn maps
> to the correct column?
>
> all the nice bind-functions don't seem to make life easier in this
> aspect.
>
> am i missing some basic (my)SQL/DBI-command or something?
>
> *confused*
>
> M.
>
>
>
>
>
--- Begin Message ---
You need to start a little earlier. Build the SQL so it has the desired
number of placeholders.
# The default error message is more thorough that your die()
$dbh -> {RaiseErrors} = 1;
# Assuming @names has the correct number of values
my ( @where, @vals );
foreach ( @names ) {
push @where, "name = ?";
push @vals, "%$_%";
}
my $sql = "SELECT * FROM my_table";
$sql .= "WHERE " . join( " AND ", @where ) if @where;
my $sth = $dbh -> prepare( $sql );
$sth -> execute( @vals );
# This will generate a brand new SQL statement every time.
# In Oracle, the parser will recognize when you use the identical text
# and use the previously parsed statement.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
----- Original Message -----
From: "Tony Vassilev" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, January 16, 2002 04:54
Subject: arbitrary number of bindings & LIKE statement
> I'm a newbie trying to get a grip on the bind_param function:
>
> If I'm trying to execute an SQL <LIKE> statement with several
placeholders,
> the following method of binding works (I get the proper data returned):
>
> my $sth = $dbh->prepare($sql) || die "couldn't prepate";
> $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:
>
> 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";
>
> And yet, fetching the results of this one returns only comparisons with
the
> last binding. So, in a case with two names, only data mathing LIKE
> %$names[1]% comes back while the first one is not compared.
>
> I don't understand... I mean, aren't those two methods effectively asking
> for the same thing? I've been fooling around for a while trying to find a
> way to execute a LIKE statement with an arbitrary number of placeholders
but
> keep running into stumbling blocks with the DBI syntax.
>
> Any help would be great! I imagine this is a very simple problem.
--- End Message ---
