Not sure how to query my database in this code

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

I posted a SOPW yesterday regarding how to make dynamic tables for an image gallery (whereas you can automagically print tables of with any number of rows and columns properly).

After playing around for most of the day today I got one that works great in the command line. I can't figure out from the code below how I'd fetch my database results. It may be due to work overload right now but I can't seem to grasp it. Can someone shine some light on this task for me?

Please forgive the not-so-perfect alignment, haven't gotten around to cleaning it up yet.

sub display_pictures
{
my $rowcnt = 5; 
my $colcnt = 5; 
my $total_images = $rowcnt * $colcnt; 
my $count = 0; 
my $tags_to_close = 0; # ensure all html tags are good
my $stop; 
my $counted_in_row = 0;

my $data = qq(SELECT id, filename, title FROM pictures WHERE 1);
my $sth = $dbh->prepare($data);
$sth->execute() or die $dbh->errstr;

print qq(<table>);

my ($id, $filename, $title);
$sth->bind_columns(\$id, \$filename, \$title);

while ($sth->fetch)
{
for( my $i=0;$i<$rowcnt;$i++) # for 1 .. 5 for rows
{ 
   print "<tr>"; 
   for(my $j=0;$j<$colcnt;$j++) # for 1 .. 5 for pictures
   { 
      $counted_in_row++;
      if ($count == $total_images)
      {
         my $tags_to_close;
         $tags_to_close = $colcnt - $j;
         $tags_to_close = 0 if $tags_to_close == $colcnt;
         
         for (1 .. $tags_to_close)
         {
            print qq(<td> . </td> );
         }    
         $stop = 1;
         last if $stop;
      }

      print qq(<td> $id $filename $title </td>\n);
      $count++; 
      last if $count = $total_images;
   } 
   print "</tr>\n"; 

   last if $stop;
} 
}

print qq(</table>);
}
[download]

Comment on Not sure how to query my database in this code Download Code

Replies are listed 'Best First'.
Re: Not sure how to query my database in this code by sulfericacid (Deacon) on May 11, 2008 at 02:34 UTC
First thing I see is most likely a mistake is `last if $count = $total_images;` [download] That should be `last if $count == $total_images;` [download] Now as for the real question. One perlish way to do it would be to query the database in whatever fashion you want and store the unique id's into an array. Since you already have a $count variable, it's easy to index when you need it. I'm sure there's a much more efficient way to do this as this method would require you to query the database for each and every image. But it's an idea. sub display_pictures { my $rowcnt = 5; my $colcnt = 5; my $total_images = $rowcnt * $colcnt; my $count = 0; my $tags_to_close = 0; # ensure all html tags are good my $stop; my $counted_in_row = 0; my @saved_results; print qq(<table>); my $data = qq(SELECT id, filename, title FROM pictures ORDER BY ID DES +C); my $sth = $dbh->prepare($data); $sth->execute() or die $dbh->errstr; my ($id, $filename, $title); $sth->bind_columns(\$id, \$filename, \$title); while ($sth->fetch) { push(@saved_results, $id); } print @saved_results; for( my $i=0;$i<$rowcnt;$i++) # for 1 .. 5 for rows { print "<tr>"; for(my $j=0;$j<$colcnt;$j++) # for 1 .. 5 for pictures { $counted_in_row++; if ($count == $total_images) { my $tags_to_close; $tags_to_close = $colcnt - $j; $tags_to_close = 0 if $tags_to_close == $colcnt; for (1 .. $tags_to_close) { print qq(<td> . </td> ); } $stop = 1; last if $stop; } #Retrieve your image from server here and #put the corresponding tags print qq(<td> $saved_results[$count++] </td>\n); # query the dat +abase via id here $count++; last if $count == $total_images; } print "</tr>\n"; last if $stop; } print qq(</table>); } [download] "Age is nothing more than an inaccurate number bestowed upon us at birth as just another means for others to judge and classify us" sulfericacid	[reply] [d/l] [select]
Re: Not sure how to query my database in this code by pc88mxer (Vicar) on May 11, 2008 at 05:23 UTC
Here's an approach based on the iterator nature of a `DBI` statement handle: `my $sth = $dbh->prepare("SELECT id, filename, title FROM pictures"); $sth->execute(); print "<table ...>"; for my $row (1..$nrows) { print "<tr>"; for my $col (1..$ncols) { print "<td>"; my $pic = $sth->fetchrow_hash; if ($pic) { # emit html for the image # data for the picture is in hashref $pic, i.e.: # $pic->{id}, $pic->{filename} and $pic->{title} } else { # emit html for an empty cell } print "</td>"; } print "</tr>\n"; } print "</table>\n";` [download] Note that there is no need to compute the number of empty `<td>` elements to create - the return value of `fetchrow_hash` (either a hashref or `undef`) will indicate what should go in the cell.	[reply] [d/l] [select]
Re: Not sure how to query my database in this code by planetscape (Chancellor) on May 11, 2008 at 07:58 UTC
Please forgive the not-so-perfect alignment, haven't gotten around to cleaning it up yet. Perltidy can help you with that. HTH, planetscape	[reply]
Re: Not sure how to query my database in this code by dragonchild (Archbishop) on May 11, 2008 at 18:31 UTC
`my @rows; my @this_row; my $num_per_row = 5; my $in_this_row = 0; while ( my $item = $sth->fetchrow_hashref ) { push @this_row, $item; if ( @this_row >= $num_per_row ) { push @rows, [ @this_row ]; @this_row = (); } }` [download] At this point, you have an AoAoH (array of arrays of hashes). You could now pass this to a Template Toolkit snippet that looks something like: `[% FOR row IN table %] <tr> [% FOR item IN row %] <td>item.id item.filename item.title</td> [% END %] </tr> [% END %]` [download] And you'd end up with something very close to what you have, output-wise. I've left limiting the number of rows to 5 and linking the two snippets as exercises for the reader. My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply] [d/l] [select]