fetchrow_array

[rb_rb]

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.

[marto]

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

[monkey_boy]

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

[bradcathey]

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

[kutsu]

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

[bradcathey]

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!