How do I handle a varying number of items from a database query?

Question

Effectively a duplicate of: How can I display data in table with Perl

The accepted answer there applies here. So do some of the alternatives.

---

I am trying to run raw database queries from Perl program and display results to the user. Something like select * from table. I want to display the information in a HTML table. The columns in the HTML table correspond with the returned columns.

I am having some issues. I can run describe table query to return the number of columns there are in the table. However, how will I store the information from the returned results into arrays?

So if I am storing results like this:

while (($f1, $t2, $n3, $k4, $d5, $e6) = $sth1->fetchrow_array)

In this case I only know that there are, say four columns (which I got from describe table). But this number four is dynamic and can change depending on the table name. I can not declare my variables based on this number. Any suggestions?

Answer 1

while (my @row = $sth->fetchrow_array)
{
        print "<tr>".join("", map{ "<td>${_}</td>" } @row)."</tr>"  ;
}

Answer 2

Use the technique suggested in the answer(s) to the other question - use fetchrow_array to fetch into an array:

while (my @row = $sth->fetchrow_array())
{
    ...process array...
}

Or use an alternative to fetchrow_array(), such as fetchrow_hashref().

Answer 3

Try:

print "<table>\n";

# display HTML header
@cols = @{$sth->{NAMES_uc}};
print "<tr>".join("", map { "<th>${_}</th>" } @cols)."</tr>\n";

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

Cheers, V.