Re: Using multiple values in a SQL "in" statement

in reply to Using multiple values in a SQL "in" statement

my @id=(1,2,3,4);
my $question_mark_string = join(',', ('?') x scalar(@id) ); # ?,?,?,?
my $sth = $dbh1->prepare("SELECT name FROM table where id in ( $questi
+on_mark_string )") or die "Couldn't prepare statement: " . $dbh1->err
+str;
$sth->execute(@id)
[download]

Comment on Re: Using multiple values in a SQL "in" statement Download Code

Replies are listed 'Best First'.
Re^2: Using multiple values in a SQL "in" statement by jhourcle (Prior) on May 02, 2013 at 02:28 UTC
I either use that logic, or when I'm lazy about typing: `my $sth = $dbh1->prepare('SELECT name FROM table WHERE id IN ( ?'.(',?' x $#id).' )') or ...` ... but I wouldn't recommend this if other people might be expected to actually maintain it.	[reply] [d/l]
Re^2: Using multiple values in a SQL "in" statement by perlvroom (Acolyte) on May 02, 2013 at 17:51 UTC
This worked great. thanks. Can you give me a brief explanation of what's happening at this line? `my $question_mark_string = join(',', ('?') x scalar(@id) );`	[reply] [d/l]
Re^3: Using multiple values in a SQL "in" statement by jhourcle (Prior) on May 05, 2013 at 13:07 UTC
It's three operations. The `x` operator, is either a string or list multiplier. Here, it's being used on an list, as the item is in parens: `('?') x scalar(@id)` `scalar(@id)` gives the number of items in the array. So we end up with a list of question marks the same size as the `@id` array. The `join` will flatten a list into a single string, placing the first argument in between each item. So we're placing a comma between each of the question marks.	[reply] [d/l] [select]

In Section Seekers of Perl Wisdom