• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

Perl DBI Group By

I have developed some pages that query an oracle database and the results are displayed on the web.  Unfortunately, I cannot workout how to display data that I want grouped together.

I would like the data displayed like this:

Company BMW
Last Name First Name
Bloggs       Jo
Smith        Harry
Jones        Fred

Company Ford
Last Name First Name
Smith        Lisa
Armstrong Phil

Instead of:
Company Last Name  First Name
BMW       Blogss        Jo
BMW       Smith         Harry
BMW       Jones          Fred      
Ford        Smith         Lisa
Ford        Armstrong  Phil

Here's a snippet of the code...
print '<div id="table"><div>';
print '<TABLE border="0" cellpadding = "4">';
my $tbl_array_ref = $sth->fetchall_arrayref();
        print '<TR>';
        $column_row = $sth->{NAME};
        foreach $column (@$column_row){
                print "<TD><B>$column</B></TD>";}
        print '</TR>';
        print '<TR>';
        foreach my $row (@$tbl_array_ref){
                print '<TR>';
                foreach my $element (@$row){
                        print "<TD><p>$element</p></TD>";
}
        print '</TR>';}
print '</TABLE>';
...
0
Selhurst100
Asked:
Selhurst100
  • 2
  • 2
1 Solution
 
kanduraCommented:
simplest solution is to keep a variable around that holds the current company. when a new row arrives with a different company, write out the appropriate html, and update the variable.

0
 
Selhurst100Author Commented:
kandura, I was thinking along those lines.  But, my Perl knowledge isn't great and I have been struggling with this all day.  Could you expand on how you would do it.
0
 
kanduraCommented:
Something like this, then? It's untested (I don't have access to your database), but it should work. I have assumed that the Company field is the first one in every row.

print '<div id="table"><div>';
print '<TABLE border="0" cellpadding = "4">';

my $tbl_array_ref = $sth->fetchall_arrayref();

$column_row = $sth->{NAME};
my $company = '';
my $company_head = shift @$column_row;

foreach my $row (@$tbl_array_ref){

    my $new_company = shift @$row;

    if($new_company ne $company) {    ### print new company header row, and column headers
        $company = $new_company;

        print qq| <tr><th colspan="99"> Company $company </th></tr>\n|;

        print '<TR>';
        foreach $column (@$column_row){
                print "<TD><B>$column</B></TD>";}
        print '</TR>';
    }

    print '<TR>';
    foreach my $element (@$row){
        print "<TD><p>$element</p></TD>";
    }
    print '</TR>';
}

print '</TABLE>';
0
 
ozoCommented:
my $company='';
foreach my $row (@$tbl_array_ref){
  if( $company ne $row->[0] ){
      print "<TR><TD>Company</TD><TD>$row->[0]</TD></TR>\n";
      print "<TR><TD>Last Name</TD><TD>First Name</TD></TR>\n";
      $company = $row->[0];
  }
  print "<TR>";
  foreach my $element (@$row[1..$#$row] ){
        print "<TD><p>$element</p></TD>";
  }
  print "</TR>\n";
}
0
 
Selhurst100Author Commented:
Thank you very much!!  It works a treat.  You have saved me hours of time!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now