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();

[download]
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);
}

    [download]

    From this page.
    Hope this helps.

    Martin

    Janitored by holli - fixed formatting

[reply]
[d/l]
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'")};

    [download]



    This is not a Signature...
[reply]
[d/l]
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'};
}

    [download]


    —Brad
    "The important work of moving the world forward does not wait to be done by perfect men." George Eliot
[reply]
[d/l]

      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";
  }

      [download]

      Updated: Added better reference to documentation

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

[reply]
[d/l]

        Thanks, but you lost me on:

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

        [download]

        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
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom