Selhurst100
asked on
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>';
...
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>';
...
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
my $company='';
foreach my $row (@$tbl_array_ref){
if( $company ne $row->[0] ){
print "<TR><TD>Company</TD><TD>$
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";
}
foreach my $row (@$tbl_array_ref){
if( $company ne $row->[0] ){
print "<TR><TD>Company</TD><TD>$
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";
}
ASKER
Thank you very much!! It works a treat. You have saved me hours of time!