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

Hello,

I want to output the contents of a DB table into an HTML table.

The DB table has 600 rows.
I'de like to have the output be formatted as an HTML table with 3 columns of 200 rows each.

<tr> <td>$indexs0-200</td> <td>$indexs201-400</td> <td>$indexs401-600</td> </tr>

I've gotten as far as outputting one long column, but this is as far as I got.
I'm thinking I need to do a while loop with a for statement in it but I'm not really sure how to do it.
Heres my code so far: Thanks - Donzo

#!/usr/bin/perl -w use DBI; use strict; use CGI; my $q; $q = new CGI; print $q->header, $q->start_html("District List"); my $dbh = DBI->connect("DBI:ODBC:PM_Pages","","") or die "Cannot conne +ct: " . $DBI::errstr; my $sql = "select District,DIST from Districts"; my $sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr +(); $sth->execute() or die "Cannot execute: " . $sth->errstr(); my $district; my $ID; while (($district,$ID) = $sth->fetchrow_array) { print ("a href=\"$ID\">$district</a> <br> "); } $sth->finish(); $dbh->disconnect; print $q->end_html;

Replies are listed 'Best First'.
Re (tilly) 1: DBI output in 3 columns
by tilly (Archbishop) on Jul 15, 2001 at 06:17 UTC
    Here is a more advanced answer. It assumes familiarity with how to use the list-oriented nature of Perl, how to use map, and how to access arrays of arrays as described in perlref.

    I would do the rearranging of data in a function. Suppose that you can create an array @cells of cells above. I assume that you want it sorted by column and then row. Here is a possible rearrangement functions you could use:

    # Takes a number of columns then a list. Reformats the list into # an array of arrays, with the original elements sorted down by # columns, and any missing items missing off of the last column sub reformat_major_minor { my $dim = shift; my $row_size = int((@_+$dim - 1)/$dim); my @ret; foreach my $i (0..$#_) { $ret[$i%$row_size][$i/$row_size] = $_[$i]; } return @ret; }
    And then you can produce your output with:
    foreach my $row (reformat_major_minor(3, @cells)) { print "<tr>\n", (map " <td>$_</td>\n", @$row), "</tr>\n"; }
    Should you dislike taking the missing elements on the last column and want to take it on the last row instead, you can use the following reformat function instead:
    # Takes a number of columns then a list. Reformats the list into # an array of arrays, with the original elements sorted down by # columns, and any missing items missing off of the last column. sub reformat_major_minor { my $dim = shift; my $row_size = int(@_/$dim); my @ret; push @ret, [splice @_, 0, $row_size] while @_; return swap_dims(@ret); } # Takes an array of arrays and swaps rows for columns. sub swap_dims { my @ret; foreach my $row_ind (0..$#_) { my $row = $_[$row_ind]; foreach my $col_ind (0..$#$row) { $ret[$col_ind][$row_ind] = $row->[$col_ind]; } } return @ret; }

    UPDATE
    Oops. I put the wrong reformatting function up there in the first slot. Now fixed.

Re: DBI output in 3 columns
by dvergin (Monsignor) on Jul 15, 2001 at 02:42 UTC
    Two possible approaches to avoid loading all the data at once. This may not matter to you in this case, but with more data, it could be an issue.

    1)Three db handles and matching statement handles: $dbh1,$sth1; $dbh2,$sth2; $dbh3,$sth3. Set each up using the same syntax. Then walk through (and ignore) the first 100 rows for $sth2. Do the same for the first 200 rows for $sth3. (There's probably a more clever way to advance handles 2 and 3 to the appropriate position.) Then in your loop, grab a value from each $sth in turn and build your table cells: 1,101,201; 2,102,202, etc.

    2)Table-in-a-cell. Make your main table have three columns but only one row. In each of the three cells of that table (which become your columns) build a second, inner table consisting of a single column and 100 rows (resulting in three inner tables: one for each column). Then build the display with an outer loop (one for each column cell) that sets up the first cell and the inner table and then goes into an inner loop that reads the data a row at a time. The first time through the inner loop it spits out items 1-100 one item per row into the table in column one. After the inner loop, close the first cell. Then on the second time through the outer loop, the inner loop spits out items 101-200 into the next table in column two. etc.
    Update: Or skip the inner table and just use <BR> tags to separate each line in the column as synapse0 (who read your question more closely) suggests. Note that, as you have defined the problem (each column is one long data cell), there is absolutely no need to read in all the data at once.

    HTH

Re: DBI output in 3 columns
by mdillon (Priest) on Jul 15, 2001 at 03:34 UTC

    try this:

    #!/usr/bin/perl -w use CGI; use DBI; use strict; use vars qw($dbh); BEGIN { $dbh = DBI->connect("DBI:ODBC:PM_Pages","","", { RaiseError => 1 }); } END { $dbh->disconnect if $dbh; } my $q; $q = new CGI; print $q->header, $q->start_html("District List"), $/; my $sql = "select District, DIST from Districts"; my $sth = $dbh->prepare($sql); $sth->execute(); my $cols = 3; my @rows = @{$sth->fetchall_arrayref}; # my $col_length = POSIX::ceil(@rows / $cols); my $col_length = (@rows - @rows % -$cols) / $cols; print "<table>", $/; for my $row (0 .. $col_length - 1) { print "<tr>", $/; for my $col (0 .. $cols - 1) { my $idx = $row + ($col * $col_length); last unless $idx < @rows; printf '<td><a href="%d">%s</a></td>', @{$rows[$idx]}[1,0]; print $/; } print "</tr>", $/; } print "</table>", $/; print $q->end_html, $/;

    notice that you need to know the total number of rows and that you need to be able to have indexed access to the result set in order to build the table by directly iterating through the cells in column-major, row-minor order. this is why all the data is fetched into an array before display.

    you specify that your table always has 600 rows, so you may be able to do this without an array, if you can get indexed (i.e. non-sequential) access to your result set. i doubt this is possible through ODBC (it probably requires a DB-specific driver and DB support for non-sequential cursors).

    update: substituted working code for broken code

Re: DBI output in 3 columns
by synapse0 (Pilgrim) on Jul 15, 2001 at 02:24 UTC
    a loop to do what you need.. loop untill we hit 200, then reset loop counter and write the end and starting TD tags... when done, end table.
    qq() is another way of stating quotes, so you don't have to escape your quotes in the html.
    my $district; my $ID; my $index_num = 0; # initialize counter print qq(<TABLE><TR><TD>\n); while (($district,$ID) = $sth->fetchrow_array) { print qq(<a href="$ID">$district</a> <br>\n); if (++$index_num == 200) { # end of index list, end TD and reset counter print qq(</TD><TD>\n); $index_num = 0; } } print qq(</TD></TR></TABLE>\n);
    Hope that gives you some ideas..
    -Syn0
Re: DBI output in 3 columns
by Masem (Monsignor) on Jul 15, 2001 at 02:25 UTC
    You're going to have to collect the data before printing if you want it the way you describe (1-200, 201-400, 401-600). That is, since it's just ID and district, store everything into a hash, then print out from there.:
    $sth->execute() or die "Cannot execute: " . $sth->errstr(); my %hash; while ( my( $district, $ID ) = $sth->fetchrow_array ) { $hash{ $district } = $ID; } # A number of ways to do it here, this is the best if you # don't know you have an exact multiple of 3.... my @keys = sort keys %hash; # sort by district my $column=0; print "<TABLE>\n"; while ( my $dis = shift @keys ) { if ( $column == 0 ) { print "<TR>\n"; } print "<TD><A HREF=\"", $hash{ $dis }, "\">", $dis, "</a></TD>"; if ( $column == 2 ) { print "</TR>\n"; $column = 0; } else { $column++; } } # Print trailing TR if needed if ( $column != 0 ) { print "</TD>\n"; } print "</TABLE>\n";

    Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
Re: DBI output in 3 columns
by jlongino (Parson) on Jul 15, 2001 at 04:01 UTC
    This code pretty much meets your specs. It can handle 0-600 data items. I've left DBI specifics out for illustration purposes. Not pretty, but clear. Would've been less kludgey if it were 1-600 instead of 0-600.
    my $ctr = 0; for ($ctr = 0; $ctr < 601; $ctr++) { $indexs[$ctr] = "-$ctr-"; } $ctr = 0; print "<html><body><table border=1>"; while ($ctr < 201) { print "<tr><td>"; if ($indexs[$ctr]) { print $indexs[$ctr]; ### first col current row ($ctr) } else { print '&nbsp;'; } print '</td><td>'; if (($indexs[$ctr + 201]) and ($ctr+201 != 401)) { print $indexs[$ctr + 201]; ### second col current row ($ctr) } else { print '&nbsp;'; } print "</td><td>"; if ($indexs[$ctr + 401]) { print $indexs[$ctr + 401]; ### third col current row ($ctr) } else { print '&nbsp;'; } print "</td></tr>\n"; $ctr++; } print "</table></body></htm>\n";