DBI fetchrow_hashref(), pointers & references

[Filip Sneppe]

Hi,

I use perl to access a MySQL database, and I have to make a lot of fairly
easy
queries, like these:

"select sourceip, sum(bytes) as sumbytes from traffic"
"select destip, sum(bytes) as sumbytes from traffic"
....

and then put the numbers in an html table.

The code that retrieves the data from MySQL looks like this:

$sth = $dbh->prepare("select sourceip, sum(bytes) as sumbytes from
traffic");
$sth->execute();

while ($ref = $sth->fetchrow_hashref())
  {
  $html .=
"<tr><td>$ref->{'sourceip'}</td><td>$ref->{'sumbytes'}</td></tr>\n";
  }

Is there any way to avoid having to use constructs like $ref->{'sourceip'}
and $ref->{'sumbytes'}, and instead use a generic approach to get an element
from the first, second, etc. column ?

The reason I'm asking this, is that, since I have to execute a lot of
queries
that are very similar in nature, yet use different columns from the
database,
I want to write a more generic subroutine that handles the creation of an
html
table, something like this:

sub create_html_table
  {
  my @sub_columntitles = ();
  my $sub_dbh          = shift; # database handle
  my $sub_query        = shift; # query string
  push @sub_columntitles, @_;   # titles of columns, same as table names in
database

  $sub_sth = $sub_dbh->prepare($sub_query);
  $sub_sth->execute();

  $sub_html = "<table border=1><tr>\n";
  for($i=0; $i < @sub_columntitles; ++$i)
    {
    $sub_html .= "<td>$sub_columntitles[$i]</td>";
    }
  $sub_html .= "</tr>\n";

  while ($sub_ref = $sub_sth->fetchrow_hashref())
    {
    $sub_html .= "<tr>";

    for ($i=0; $i < @sub_columntitles; ++$i)
      {
#                      Here's where I'm stuck !
#                               |
#                               V
      $sub_html .= "<td>$ref->????????????</td>";
      }
    $sub_html .= "</tr>\n";
    };
  $sub_sth->finish();

  $sub_html .= "</table>\n\n"

  return $sub_html;
  }


Thanks in advance,
Filip


-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]