On 12/8/05, Rob Kinyon <[EMAIL PROTECTED]> wrote:
> On 12/7/05, Sean Davis <[EMAIL PROTECTED]> wrote:
> > Select id from table where name in ('q','w');
>
> Yes, that will technically work. However, how does your solution tell
> me which id corresponds to q and which to w?
>
> Much better would be:
> SELECT name, id
> FROM table
> WHERE name IN ( 'q', 'w' )
>
> The Perl for this could look something like:
>
> sub get_name_ids {
> my @names = @_;
>
> my $sql = 'SELECT name, id FROM table WHERE name IN (';
> $sql .= join( ',', ('?') x @names );
> $sql .= ')';
>
> my $sth = $dbh->prepare( $sql );
> $sth->execute( @names );
>
> my $results = $sth->fetchall_hashref;
>
> return $results;
> }
>
> This will return something that looks like:
> $results = [
> { id => 3, name => 'w' },
> { id => 5, name => 'q' },
> ];
And you pull that info directly into a hash using DBI like this:
my $ary_ref = $dbh->selectcol_arrayref(
"SELECT name, id FROM table WHERE name IN ( 'q', 'w' )",
{ Columns=>[1,2] }
);
my %results = @$ary_ref;
Which should result in this:
%results = (
q => 3,
w => 5,
);
caveat - I'm not sure how well that will work with NULL values
Cheers,
Cees
---------------------------------------------------------------------
Web Archive: http://www.mail-archive.com/[email protected]/
http://marc.theaimsgroup.com/?l=cgiapp&r=1&w=2
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
