tomazos has asked for the wisdom of the Perl Monks concerning the following question:

One line of perl that assigns a multi-row single-column DBI select to an @array.

I want to select a set of items by ids on some predicate '--PREDICATE--' from an SQL table via DBI.

The long way: 

    use DBI; my $dbh = DBI->connect(...);

    ....

    my @ids = ();

    my $sth = $dbh->prepare('select id from items where '--PREDICATE--
+');

    $sth->execute();
    while (my ($id) = $sth->fetchrow_array()) {
        push @ids, $id;
    }

    $sth->finish();

    ....

[download]

I want a shorter one line version.

I am sure there exists some neat contortion using $dbh->select(col|row|all)_(array|hash)(ref)? and referencing/dereferencing - I just can't see it (and am no good with arrays of hash of reference to hash list tap dancing -- $ref->2<-{3,4}[0]{2} -- :P )

-Andrew.

Andrew Tomazos  |  andrew@tomazos.com  |  www.tomazos.com

Replies are listed 'Best First'.
Re: DBI select ids to @ids
by rnahi (Curate) on Aug 10, 2005 at 07:11 UTC

    Did you look at the Monastery Tutorials?

    DBI Recipes has a section on just that idiom you are asking about (and much more).

[reply]
Re: DBI select ids to @list
by ikegami (Patriarch) on Aug 10, 2005 at 05:31 UTC

    I think this will do the trick:

    use DBI;
my $dbh = DBI->connect(...);
my $stmt = 'select id from items where foo > 42';
my @ids = map { $_->[0] } @{ $dbh->selectall_arrayref($stmt) };

    [download]

    map is used to convert an array of rows of fields into an array of (what's in the first field of each row).

[reply]
[d/l]
[select]
      Hmmm. Learning from your example it looks like I might be able to use selectcol_arrayref also:

      @ids = @{ $dbh->selectcol_arrayref($stmt) }

      Or am I wrong here? It looks like selectcol_arrayref has some magic to select multiple columns but the manual says it defaults to the first. But is it a reference to a an array of references to an array, or just a reference to an array?

      -Andrew.

      Andrew Tomazos  |  andrew@tomazos.com  |  www.tomazos.com
[reply]
[d/l]
        ah yes, selectcol_arrayref would do the trick. That's exactly why it was written, I bet. I had forgotten about that function 
        use DBI;
my $dbh = DBI->connect(...);
my $stmt = 'select id from items where foo > 42';
my @ids = @{ $dbh->selectcol_arrayref($stmt) };

        [download]
[reply]
[d/l]
Re: DBI select ids to @ids
by mifflin (Curate) on Aug 10, 2005 at 05:38 UTC
    how about 
    @ids = @{$dbh->selectcol_arrayref('select id from table where foo > 42
+', { Columns => [1]})};

    [download]
    update

    tomazos is right, you can get rid of the Columns if you are only selecting one column 
    @ids = @{$dbh->selectcol_arrayref('select id from table where foo > 42
+')};

    [download]
[reply]
[d/l]
[select]
      If this works, and the manual says it defaults to one, you can drop the \%attr column and just write @ids = @{$dbh->selectcol_arrayref('select id from table where foo > 42')};

      Why isn't there a $dbh->selectcol_array? There is a $dbh->selectrow_array. Thats weird.

      It should be just: 

      @ids = $dbh->selectcol_array('select id from items where foo > 42');  
+# SHOULD WORK BUT WRONG

      [download]

      Or am I missing something?

      -Andrew.

      Andrew Tomazos  |  andrew@tomazos.com  |  www.tomazos.com
[reply]
[d/l]
[select]
        Why no selectcol_array?
        I'm not sure. You could always email Tim Bunce and ask him.
        However, dereferencing an array reference is easy. I really don't see a need for it.
        Personally, I wouldn't dereference it into an array. You're not showing how your are using the @ids array but if you need to pass it around in your code, say as a parameter to another sub, you will probably want it as a reference anyway.
[reply]

Back to Seekers of Perl Wisdom