Re: fetchrow_array

[bradcathey]

in reply to fetchrow_array

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!

[kutsu]

bind_columns is binding columns ;), seriously though: NAME_lc are the lower case column names from the database, region and sales in this case, and these are in an array ref which is used as the keys for %row (using a hash slice). The reason it uses bind_columns is that it processes the columns one by one (allowing you to have a normal hash not a hash of an array). So it's doing something like this:

  $sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } ));
  # NAME_lc = lower case column name
  #    so it's equal to \@row{\@region} (and \@row{\@sales})
  # binds \$row{region} much as it would \$region
  while($sth->fetch)
  {
    # sets $row{region} with region's data
    # does same with sales
    print "$row{region}, $row{sales}\n";
    # since this is rebinded each time %row is overwritten
  }

I hope this helps

[injunjoel]

Greetings all,
Here is my crack at it.
we know that

$sth->{NAME_lc};

returns the column names selected and lower-cased. with that we then utilized those names as a hash slice from the hash %row with the

@row{ @{$sth->{NAME_lc}}};

part. So up to this point we have a hash %row with keys equal to the lower-cased column names returned from the query. So next we reference the slice for the bind_columns call, which we accomplish by doing an \ on the entire list; the same as saying

$sth->bind_columns(\($var1, $var2, $var3));

which would create references for each var in the list.
Nice way of doing it and probably one I will adopt in the future since I mostly use either fetchrow_hashref or just bind explicit vars.
Does that make sense?

-InjunJoel