http://www.perlmonks.org?node_id=461017

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

Hallo, I want to get out a list of values out of my database. I tried out the method fetchrow_array and expected to receive an array. But I only got the first matching value, not all. What can be the mistake? And this is the code:
$sql = "select status from sh_protokoll where asek_jn = 'j'"; $sth = $dbh->prepare($sql); $res = $sth->execute(); @ary = $sth->fetchrow_array(); $res = $sth->finish();
Many thanks R.B.

Replies are listed 'Best First'.
Re: fetchrow_array
by marto (Cardinal) on May 27, 2005 at 10:55 UTC
    Hi,

    fetchrow_array
    This method fetches the next row of data and returns it as an array of field values.

    Example:
    while(@row = $sth->fetchrow_array) { print qw($row[0]\t$row[1]\t$row[2]\n); }

    From this page.
    Hope this helps.

    Martin

    Janitored by holli - fixed formatting

Re: fetchrow_array
by monkey_boy (Priest) on May 27, 2005 at 11:09 UTC
    there will only ever be one value in the array when you select one value from the database, i think you are confusing rows with columns, try:
    my @ary = @{$dbh->selectcol_arrayref("select status from sh_protokoll +where asek_jn = 'j'")};



    This is not a Signature...
Re: fetchrow_array
by bradcathey (Prior) on May 27, 2005 at 11:22 UTC

    Or to grab all the rows in column-value pairs into a hash ref (handy for HTML::Template),try:

    $ary = $sth->fetchall_arrayref({}); for my $i ( 0 .. $#$ary ) { print $ary->[$i]{'status'}; }


    —Brad
    "The important work of moving the world forward does not wait to be done by perfect men." George Eliot

      Alternatively you can use bind_columns to do this (except row by row instead of all at once). From the docs (prepare something like "select region, sales from table"):

      $sth->execute; my %row; $sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } )); while ($sth->fetch) { print "$row{region}: $row{sales}\n"; }

      Updated: Added better reference to documentation

      "Cogito cogito ergo cogito sum - I think that I think, therefore I think that I am." Ambrose Bierce

        Thanks, but you lost me on:

        $sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } ));

        Can you unpack that for me? It thought binding is related to placeholders. What exactly is bind_columns doing and what is the gain? Appreciate it, TIA!


        —Brad
        "The important work of moving the world forward does not wait to be done by perfect men." George Eliot