How can I display data in table with Perl?

Question

I am getting data from a database. It has 4 rows and 6 columns. I want to show this in an HTML table but am having problems. I've made the TH for the 6 columns. However, I am having problems in displaying the 4 rows. This is what I have so far:

while (($f1, $t2, $n3, $k4, $d5, $e6) = $sth1->fetchrow_array)
{
        push (@first, $f1);
        push (@second, $t2);
        push (@third, $n3);
        push (@fourth, $k4);
        push (@fifth, $d5);
        push (@sixth, $e6);
}

@first, @second...and others are arrays

While dsplaying data if I do:

foreach (@first)
{
        print "<td>$_</td>";
}

That is displaying data vertically but I want it to display horizontally.

Answer 1

The code in the question is written using Perl's DBI (DataBase Interface):

print "<table>\n";
while (($f1, $t2, $n3, $k4, $d5, $e6) = $sth1->fetchrow_array)
{
    print "  <tr>\n";
    print "    <td>$f1</td>\n";
    print "    <td>$t2</td>\n";
    print "    <td>$n3</td>\n";
    print "    <td>$k4</td>\n";
    print "    <td>$d5</td>\n";
    print "    <td>$e6</td>\n";
    print " </tr>\n";
}
print "</table>\n";

Or you could read the row into an array and then print the array more succinctly:

print "<table>\n";
while (my(@row) = $sth1->fetchrow_array)
{
    print "  <tr>\n";
    print "    <td>$val</td>\n" foreach my $val (@row);
    print " </tr>\n";
}
print "</table>\n";

Answer 2

Is this closer to what you are trying to achieve?

# display one HTML table row for each DB row
print "<table>\n";
while (my @row = $sth1->fetchrow_array) {
  print "<tr>".join("", map { "<td>${_}</td>" } @row)."</tr>\n";
}
print "</table>\n";

Cheers, V.

Answer 3

You could also use HTML::Table.

use strict;
use warnings;
use HTML::Table;

my $table = HTML::Table->new( 
    -cols    => 6, 
    -border  => 1,
    -padding => 1,
    -head => [qw( f1 t2 n3 k4 d5 e6 )],
);

# do query here.    

while ( my @row = $sth->fetchrow_array ) {
    $table->addRow(@row);
}

$table->print;

Answer 4

If you want a little more flexibility without having to write as much code, you might also want to check out HTML::Table::FromDatabase. It does the HTML for you and gives you the freedom to format things easily. It takes a little practice, but it has become an invaluable took in my arsenal.

Here is some sample code that will give you an idea of what you can do with it

use DBI;
use HTML::Table::FromDatabase;
use HTML::Table;
use CGI;

# Start the CGI wrapper
$query = new CGI();

# Just build the STH like normal, with either the SQL in-line or an $sql variable
my $sth = $mysql_dbh->prepare("SELECT DISTINCT field1, field2 FROM table ORDER BY field1");
$sth->execute;

# Tell the module to build the table using the STH you executed before
my $table = HTML::Table::FromDatabase->new(-sth=>$sth,
                                           -border=>1,  # We want a border on the entire table
                                           -callbacks=>[
                                                {
                                                        # On the switch column, we want to add an HREF with the data in the URL
                                                        column=>'field1',
                                                        transform=>
                                                                sub { $_ = shift; qq[<A HREF="$_">$_</A>]; },
                                                },
                                                {
                                                        # This example lets you return a value based on what value is in the field
                                                        column=>'field2',
                                                        transform=>
                                                                sub { $text = shift; DidItMatch($text); },
                                                }],
                                           # The two rows below are used to set CSS classes on even and odd rows if you want different formatting
                                           -evenrowclass=>'evenrow',
                                           -oddrowclass=>'oddrow'
                                           );

# Take the entire table and align all of the 2nd column to center
$table->setColAlign(2,'center');

# The following lines are just demonstrations of what else can be changed. There are a lot, so look at HTML::Table to get all of them
#$table->setColBGColor(2,'red');                # Sets the background color of all cells in a column
#$table->SetColVAlign(2,'middle');              # Sets the vertical alignment of all cells in a column
#$table->setColWidth(2,100);                    # Sets the width of a column. Could also use setColWidth(2,'50%');
#$table->setColNoWrap(2,1);                     # Sets the no-wrap property of a colum. 0=Wrap 1=NoWrap

# The following lines allow you to retrieve information about the table that was created.
my $row_count = $table->getTableRows;          # Retrieves the number of rows in the table
my $column_count = $table->getTableCols;       # Retrieves the number of columns in the table

# Finally, print the finalized table HTML
$table->print;

# Wrap up the STH and DBH if needed
$sth->finish;
$mysql_dbh->disconnect;


sub DidItMatch
{
        my $text = shift;
        if ($text eq 'Good text')
        {
                return "Matched";
        } else
        {
                return "$text";
        }
}