coldfingertips has asked for the wisdom of the Perl Monks concerning the following question:

In regards to a SOPW a few days ago (closely related but you don't need to read it to understand this problem).

I have a MySQL table "visit" that has colums: id, engine_id, useragent, ip, date. What's the easiest way to retrieve the data where you can print/manipulate it easily?

Right now I am using the below code but it only retrieves ONE record for some reason when there is 20-30 in the database. 

   my $useragent = "SELECT useragent FROM visit
   WHERE engine_id = 'Junk'";
   $sth = $dbh->prepare($useragent);
   $sth->execute() or die $dbh->errstr;

   my @row = $sth->fetchrow_array;
   foreach (@row)
   {
      print "<center>$_</center>";
   }

[download]
You notice I am only printing back the useragent right now. This is because I don't understand how I could pull back all the needed columns at one time and be able to manipulate them freely.

Using the following code everything I need is in one glob of data @row. How am I supposed to created HTML tables in such a specific order when it's one massive variable?

my $data = "SELECT engine_id,useragent,ip,date FROM visit
WHERE engine_id = 'Junk'";
$sth = $dbh->prepare($data);
$sth->execute() or die $dbh->errstr;
while (my @row = $sth->fetchrow_array) { print "$_<br>" for @row }

[download]
One idea I had was demonstrated with the first code sample. I could run through all that code each time for each column and store it in @row1, @row2, @row3 etc. But that seems to be a ton of extra coding which is needed for something as ordered data printing like this. And then you'd have to worry about making sure all the variables' data matches up when you display it on the screen.

In short what I'm trying to do is print in a format like: 

<td>COUNT (ID)</td> <td>UserAgent</td> <td>IP</td> <td>time</td> <td><
+/td><tr>
 ...... again ....

[download]
As much as I'd like code to do just that I'd really appreciate also having an explanation of what you did. I'd much rather understand it so I don't have to ask this again next time I do something like this.

Thank you everyone.

Replies are listed 'Best First'.
Re: printing records in MySQL
by dragonchild (Archbishop) on Feb 01, 2005 at 01:49 UTC
    Read the DBI documentation. You're using fetchrow_array once. You have a few options:
    1. my $data = fetchall_arrayref({})

      This has the advantage of pulling it back into something HTML::Template understands.

    2. while (my @row = $sth->fetchrow_array)

      This is useful if you want to do processing on each row as it comes back instead of pulling everything back all at once

    Being right, does not endow the right to be rude; politeness costs nothing.
    Being unknowing, is not the same as being stupid.
    Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
    Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

[reply]
[d/l]
[select]
Re: printing records in MySQL
by punkish (Priest) on Feb 01, 2005 at 02:45 UTC
    start by understanding how a record set from a db query is represented by the Perl data structures. There are primarily two ways --

    Each record can be either an array of columns or a hash with column-names as hash keys and column-values as the corresponding hash values.

    But, that is only one record. Several records are treated as arrays.

    So, a complete record set (be it of one record or multiple records) is an array of arrays (aoa) or an array of hashes (aoh).

    Once you have the record set, loop through the array, deref the elements into the corresponding array elements or hash keys, and print the values.

    If you get it a row at a time (as you are doing), do what dragonchild suggests. Put it in a while loop. If you bring it all at once, you have the entire structure anyway. Use HTML::Template and you have the aoh ref ready to feed to it... no further assembly required.

[reply]
Re: printing records in MySQL
by jZed (Prior) on Feb 01, 2005 at 03:26 UTC
    I'd agree that HTML::Template with fetchall_arrayref({}) is the easiest way to go.

    However, it's quite possible to do it with DBI and a loop. The DBI bind_columns() method is the fastest fetching method and gives you nice variable names without even creating a hash. Here's an example: 

    my $sth=$dbh->prepare("SELECT country,region FROM geo");
$sth->execute;
my($country,$region);
$sth->bind_columns(\$country,\$region);
while ($sth->fetch) {
    print "<tr><td>$country</td><td>$region</td></tr>\n";
}

    [download]
[reply]
[d/l]
Re: printing records in MySQL
by nedals (Deacon) on Feb 01, 2005 at 04:23 UTC

    Right now I am using the below code but it only retrieves ONE record for some reason when there is 20-30 in the database.

    How may records do you have where engine_id='Junk'?
    Or do you want ALL records. If so, drop the where clause.

    my $useragent = "SELECT engine_id,useragent,ip,date FROM visit WHERE e
+ngine_id='Junk'";
$sth = $dbh->prepare($useragent);
$sth->execute();

#__THIS__
$sth->bind_columns(\my($engine_id,$useragent,$ip,$date));
while ($sth->fetch()) {;
    print "<td>$engine_id</td><td>$useragent</td><td>$ip</td><td>$date
+</td>";
}

#__OR THIS__
while (my @row = $sth->fetchrow_array()) {
    print "<td>$row[0]</td><td>$row[1]</td><td>$row[2]</td><td>$row[3]
+</td>";
}

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom