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

Okay, I am sure I am just missing something simple here...

I have a piece of code that needs to get all of the fields for a certain column in a table and I want them stored in an array. I have been trying to use: 
my $sth=$dbh->prepare(qq~select "$myColumn" from $myTable~);
$sth->execute();
my @fields=$sth->fetchrow_array;

[download]
However, this is just returning the value of the first field.
Am I using fetchrow_array() wrong? Is there another way using fetchrow_arrayref() that would be better?

Thank you in advance.

Replies are listed 'Best First'.
Re: Just another DBI question.
by rob_au (Abbot) on Jan 18, 2002 at 16:24 UTC
    You are very close on this one - I think you will find that it is the selectcol_arrayref method detailed in the DBI documentation that you require. eg.

    my $arrayref = $dbh->selectcol_arrayref(qq/
    SELECT  $myColumn
    FROM    $myTable
/);
my @array = @{$arrayref};

    [download]

    Note that the returned data structure is an array reference rather than an array, hence the second line to dereference the data structure. If you are not familiar with this terminology, you can read up on references in perlref.

     

    perl -e 's&&rob@cowsnet.com.au&&&split/[@.]/&&s&.com.&_&&&print'

[reply]
[d/l]
[select]
      Ah, yes, this is exactly what I had wanted.
      Thank you.
      I guess that I missed reading: 
      In a scalar context, fetchrow_array returns the value of the first fie
+ld. An undef is returned if there are no more rows or if an error occ
+urred. Since that undef can't be distinguished from an undef returned
+ because the first field value was NULL, you should exercise some cau
+tion if you use fetchrow_array in a scalar context.

      [download]
      from the DBI perldoc
      Also, I suppose that I could be using the other select*_*() DBI functions instead of the extra prepare and execute calls.

      -xtype
[reply]
[d/l]
Re: Just another DBI question.
by George_Sherston (Vicar) on Jan 18, 2002 at 16:14 UTC
    The fetchrow_array just gets you a single row. You can use it in a while loop to get all the rows one by one: 
    while (my @fields=$sth->fetchrow_array) {
   Do_Stuff(\@fields);
}

    [download]
    Or you can get the whole lot into an array reference in one gulp with 
    my $fields = $sth->fetchall_arrayref;

    [download]


    § George Sherston
[reply]
[d/l]
[select]
Re: Just another DBI question.
by n3dst4 (Scribe) on Jan 18, 2002 at 16:47 UTC 
    my @values = map {$_->[0]} @{$sth->fetchall_arrayref};

    [download]

    ...if your just getting one value out of each row and you want an array of them, which is what I think you're saying.

[reply]
[d/l]
[reply]
Re: Just another DBI question.
by jonjacobmoon (Pilgrim) on Jan 18, 2002 at 19:22 UTC
    If you would like to be able to access specific columns then you might consider using fetchorw_hashref. This gets each row one at a time as fetchrow_array does but allows you to access by name instead of having to remember which column was where.

    So, 

    while (my $row = $sth->fetchrow_hashref)
{
    print "The data in this_col is $row->{'this_col'}\n"; 
    push(@rows,$row)  # array of hashrefs or the whole table in a data
+ structure
}

    [download]

    I like this better than the other solutions bec it is clean, easy to read and best of all easy to use and flexible.

    I admit it, I am Paco.
[reply]
[d/l]

Back to Seekers of Perl Wisdom