Controlling order of fields in report

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

The following snippet of code is generating correct result files, but I would like to be able to control the order of the fields in the report. My preference would be that the fields be listed in the report in the same order as they are named in the table (or query) that the DBI statement handle is operating on.

I understand that hashes are unordered. But surely someone has devised a simple and elegant way to address this issue before I grappled with it. Any ideas out there?

-- Hugh

  my $j = 0;
  my($line,$field,$record);
  RECORD: while ($record = $sth->fetchrow_hashref){
    $line = '';
    if($j == 0){
      foreach $field (keys %$record){
        $line .= $field . "\t";
      }
      print RESULTS $line,"\n";
      $j++;
      next RECORD;
    }
    $j++;
    foreach $field (keys %$record) {
      if (!defined($record->{$field})) {
        $record->{field} = '';
      } else {
          $record->{$field} =~ s/\t/ /g;
          $record->{$field} =~ s/\r//g;
          if (/\n/) {
              $record->{$field} = s/(\s)\n/$1/g;
              $record->{$field} = s/\n(\s)/$1/g;
              $record->{$field} = s/\n$//;
              $record->{$field} = s/\n/ /g;
          }
      }
      $line .= $record->{$field} . "\t";
    }
    print RESULTS $line,"\n";
  }
  close(RESULTS);
[download]

UPDATE:

bobf:

You're the man!

That is exactly what I was looking for. And here is the patch which did the trick. Of course I had to apply it in two places, to the loop which created the header of field names, as well as to the loop which constructed each record in the report.

-      foreach $field (keys %$record){
+      foreach $field (@{$sth->{NAME}}) {
[download]

Now everything appears in the order defined by the query, even if I just grab a SELECT * FROM table; and the order is given by the organization of the table queried. Thanks again.

if( $lal && $lol ) { $life++; }

Comment on Controlling order of fields in report Select or Download Code

Replies are listed 'Best First'.
Re: Controlling order of fields in report by samtregar (Abbot) on Oct 15, 2007 at 17:56 UTC
You didn't show the part where you setup the query for $sth. But let's imagine it looked like: `my $sth = $dbh->prepare("SELECT foo, bar, baz FROM bif");` You could replace that with: `my @cols = qw(foo bar baz); my $sth = $dbh->prepare("SELECT " . join(', ', @cols) . " FROM bif" +);` [download] Now, instead of iterating over `keys(%$record)`, just iterate over `@cols`, in the original order: `foreach $field (@cols) {` Of course there are lots of other ways to do it, but that's how I'd do it. -sam	[reply] [d/l] [select]
Re: Controlling order of fields in report by NetWallah (Canon) on Oct 15, 2007 at 18:01 UTC
How about using "fetchrow_arrayref" ? sample code snippet(Uses selectall_arrayref): my %Rec = ( # Table names, and Field attribs+Processing methods messages =>[ ["id", sub{sprintf "%3d",@_}], ["date", ,\&MyDateConverter], ["body", sub{substr($_[0] . " " x 26,0,26)}], #Max 2 +6 ["fromid", sub{substr($user[$_[0]],0,10) . "->"}], ["toid", sub{"->" .substr($user[$_[0]],0,10)}], ["computerid",sub{substr($computer[$_[0]],0,10)}], ["contenttypeid", sub{substr($contenttype[$_[0]],0,10 +)}], ["cs_call_id", sub{$_[0]}] ], usagesummary =>[ ["id", sub{sprintf "%3d",@_}], ["date", \&MyDateConverter], ["fromid", sub{substr($user[$_[0]],0,10) . "->"}], ["toid", sub{"->" .substr($user[$_[0]],0,10)}], ["messagecount", sub{sprintf "%3d msgs",@_}], ["cs_call_id", sub{$_[0]}] ] ); my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time); exists $ENV{TZ} or $ENV{TZ}= $isdst? "PDT" : "PST"; Load_Table("users", \@user , 0); Load_Table("computers", \@computer, 1); Load_Table("contenttypes",\@contenttype, 0); Load_Table("DbConfigInt", \%DbConfigInt, 1); Print_Details($_) for sort keys %Rec; #--------------------------------------- sub Load_Table{ my ($tableName,$arrayref,$printIt)=@_; my $count = 0; for ( @{$dbh->selectall_arrayref("SELECT * FROM $tableName")} ){ $count++; $printIt and print "\t $_->[0]\t$_->[1] [$tableName]\n"; if (ref($arrayref) eq "HASH"){ $arrayref->{$_->[0]} = $_->[1]; next; } $arrayref->[$_->[0]] = $_->[1]; # Index by Number, get the name. } print "\t---- Loaded $count entries from $tableName.\n"; } [download] "As you get older three things happen. The first is your memory goes, and I can't remember the other two... " - Sir Norman Wisdom	[reply] [d/l]
Re: Controlling order of fields in report by bobf (Monsignor) on Oct 16, 2007 at 03:01 UTC
Is the DBI statement handle attribute 'NAME' what you're looking for? NAME (array-ref, read-only) Returns a reference to an array of field names for each column. The names may contain spaces but should not be truncated or have any trailing space. Note that the names have the letter case (upper, lower or mixed) as returned by the driver being used. Portable applications should use NAME_lc or NAME_uc. `print "First column name: $sth->{NAME}->[0]\n";` [download]	[reply] [d/l]