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

I have an array "@row = $sth->fetchrow_array" that comes from a database query:

$dbh = DBI->connect("DBI:mysql:$database:$db_server", $user, $password);

$statement = "SELECT city FROM speedtrap WHERE $FORM{state}";

$sth = $dbh->prepare($statement) or die "Couldn't prepare the query: $sth->errstr";

$rv = $sth->execute or die "Couldn't execute query: $dbh->errstr";

$rc = $sth->finish;
$rc = $dbh->disconnect;

So my @row array should have a list of cities that exist for a specific state that can be unlimited in length. I want to print these cities in a HTML table that has four columns: 

<TR>
                                    <TD width="25%">
<DIV align=center><FONT 
color=#2f2cff>Wheaton</FONT><BR></DIV></TD>
                                    <TD width="25%">
<DIV align=center><FONT 
color=#2f2cff>Chicago</FONT><BR></DIV></TD>
                                    <TD width="25%">
<DIV align=center><FONT 
color=#2f2cff>Gurnee</FONT><BR></DIV></TD>
                                    <TD width="25%">
<DIV align=center><FONT 
color=#2f2cff>Lisle</FONT><BR></FONT></DIV></TD>
                                </TR>

[download]
Obviously the city names in the HTML above should be automatically generated from the array and not statically printed.

How can I make a PERL loop that prints my table automatically?

Thanks,
Adam

Replies are listed 'Best First'.
Re: Print Array into 4 Column Table
by diotalevi (Canon) on Jun 25, 2004 at 18:06 UTC

    splice(). Here's a snippet for you.

    use CGI qw( Tr td div font br );

while ( @cities )
{
    my @row = splice @cities, 0, 4;
    print join( "",
                map( Tr( td( { -width => '25%', },
                             div( { -align => 'center', },
                                  font( { -color => '#2f2cff' },
                                        $_ )
                                  . br() ) ) ),
                     @row ) );
}

    [download]
[reply]
[d/l]
Re: Print Array into 4 Column Table
by jeffa (Bishop) on Jun 25, 2004 at 18:12 UTC

    "How can I make a PERL loop that prints my table automatically?"

    You could use DBIx::XHTML_Table instead. But if you need serious flexibility, you should look into HTML::Template or Template Toolkit. But, if you don't, that first module does a pretty good job, if you don't mind me saying so myself. ;) 

    use DBIx::XHTML_Table;

my $table = DBIx::XHTML_Table->new($dbh);

$table->exec_query("SELECT city FROM speedtrap WHERE $FORM{state}");

print $table->output;

    [download]
    However, i don't like how you interpolate $FORM{state} like that. You should be providing a selectbox of states and use integers for the values. Then you could use DBI's bind vars to safely select the correct state: 
    
my $state_id = $params->{state_id};

#untaint $state_id somehow

$table->exec_query('
    SELECT city
    FROM speedtrap
    WHERE state.id = ?
',[$state_id]);

    [download]
    See perldoc DBI for more on bind vars.

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
[select]
Re: Print Array into 4 Column Table
by idsfa (Vicar) on Jun 25, 2004 at 18:27 UTC

    DBD::Anydata is nice for this ...

    use DBI;

$dbh = DBI->connect("DBI:mysql:$database:$db_server",
                    $user, $password);
$statement = "SELECT city FROM speedtrap WHERE $FORM{state}";

my $anydbh = DBI->connect('dbi:AnyData(RaiseError=>1):');
$anydbh->func( 'combo', 'DBI', $dbh, {sql=>$statement},'ad_import');

print $anydbh->func( 'combo', 'HTMLtable', 'ad_export' );

    [download]
    If anyone needs me I'll be in the Angry Dome.
[reply]
[d/l]
Trying to print HTML table, erroring out.
by Anonymous Monk on Jun 26, 2004 at 04:33 UTC
    I have the following code that is erroring out with "Software error: Couldn't execute query: DBI::db=HASH(0x820c7ac)->errstr at cityshow.pl line 34.:

    Line 34 reads '$rv = $sth->execute or die "Couldn't execute query: $dbh->errstr";'

    Here is the contents of my perl script, it is supposed to print the cities of a state in a table that is 4 columns wide:

    
#!/usr/local/bin/perl

use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);

$buffer = $ENV{'QUERY_STRING'};

@pairs = split(/&/, $buffer);
foreach $pair (@pairs) {
  ($name, $value) = split(/=/, $pair);
  $name =~ tr/+/ /;
  $name =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
  $value =~ tr/+/ /;
  $value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
  $FORM{$name} = $value;
}


##Start database connections##########################################
+##########
$database = "database";
$db_server = "localhost";
$user = "user";
$password = "password";

##Connect to database, insert statement, & disconnect ################
+##########
$dbh = DBI->connect("DBI:mysql:$database:$db_server", $user, $password
+);

$statement = "SELECT city FROM database WHERE $FORM{state}";


$sth = $dbh->prepare($statement) or die "Couldn't prepare the query: $
+sth->errstr";

$rv = $sth->execute or die "Couldn't execute query: $dbh->errstr";

$rc = $sth->finish;
$rc = $dbh->disconnect;
######################################################################
+##########






print "Content-type: text/html\n\n";

#*******************************************Print to the first half of
+ page
print <<EOF;

<head>
<title>xxx</title>
<meta http-equiv="Content-Type" content="text/html; charset=xxx">
<link href="xxx.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
.style1 {font-size: 36px}
-->
</style>
</head>
<body bgcolorxxx" leftmargin="0" topmargin="0" marginwidth="0" marginh
+eight="0">
<table bgcolor="#FFFFFF" id="xxx" width="xxx" height="xxx" border="0" 
+cellpadding="0" cellspacing="0" align="center">
        <tr>
                  <td colspan="5">
                          <img src="xxx.gif" width="543" height="27"><
+/td>
                  <td colspan="2">
                          <a href="../"><img src="xxx.gif" width="xx" 
+height="xx" border="0"></a></td>
                  <td colspan="2">
                          <a href="https://xxx.htm"><img src="xxx.gif"
+ width="x" height="x" border="0"></a></td>
                  <td>
                          <a href="xxx.htm" class="xxx" target="_blank
+"
 onclick="window.open('../xxx.html','xxx','height=xxx,width=xxx,left=x
+xx,top=xxx,toolbar=0,resizable=0,menubar=0'); return false"><img src=
+"x.gif" width="102" height="27" border="0"></a></td>
          </tr>
        <tr>
                <td rowspan="3">
                        <img src="images/xxx.jpg" width="x" height="x"
+></td>
                <td colspan="2" rowspan="3">
                        <img src="x.jpg" width="x" height="x"></td>
                <td colspan="3">
                        <img src="x" width="x" height="x"></td>
                <td colspan="4" rowspan="3">
                        <img src="x.jpg" width="x" height="x"></td>
        </tr>
        <tr>
                <td colspan="3">
                        <img src="x.gif" width="x" height="x"></td>
        </tr>
        <tr>
                <td colspan="3">
                        <img src="x.jpg" width="x" height="x"></td>
        </tr>
        <tr>
                  <td>
                          <img src="x.jpg" width="x" height="x"></td>
                  <td>
                          <a href="x.htm"><img src="x.gif" width="x" h
+eight="x" border="0"></a></td>
                  <td colspan="2">
                          <a hrefx"><img src="x.gif" width="x" height=
+"x" border="0"></a></td>
                  <td>
                          <a><img src="x.gif" width="x" height="x" bor
+der="0"></a></td>
                  <td colspan="3">
                          <a href=x"><img src="x.gif" width="x" height
+="x" border="0"></a></td>
                  <td colspan="2">
                          <a href=x"><img src="x.gif" width="x" height
+="x" border="0"></a></td>
          </tr>
        <tr>
                <td colspan="10">
                        <table id="x" width="x" height="x" border="0" 
+cellpadding="0" cellspacing="0">
                <tr>
                    <td height="1" colspan="3" nowrap valign="top"><di
+v class="tabs">
                            <ul id="tablist">
                                <li id="overview" class="">
             <a href="x">x</a>
</li>
                                <li id="x" class="selected">
             <a href="x.html">x</a>





                                </ul>
  <div style="clear:left;"></div>
  </div>
                            <p align="center">

                            <script language="JavaScript" src="../x.js
+">

                            </script>
<FONT face="Arial Black" size=+1>$FORM{state} x</FONT></p>

                            <P><DIV align=center></DIV>
<TABLE cellSpacing=2 cellPadding=7 width="90%" align=center border=1>
                                <TBODY>

EOF
#********************************************** Stop printing the firs
+t half of the page

$x=1;
while (@row = $sth->fetchrow_array;) {
     print <<EOF;

<TR>
                                    <TD width="25%">
<DIV align=center><FONT size=-1><B><FONT
color=#2f2cff>$row[$x]</FONT><BR></B></FONT></DIV></TD>
                                    <TD width="25%">
<DIV align=center><FONT size=-1><B><FONT
color=#x>$row[$x+2]</FONT><BR></B></FONT></DIV></TD>
                                    <TD width="25%">
<DIV align=center><FONT size=-1><B><FONT
color=#x>$row[$x+3]</FONT><BR></A></B></FONT></DIV></TD>
                                    <TD width="25%">
<DIV align=center><FONT size=-1><B><FONT
color=#x>$row[$x+4]</FONT><BR></B></FONT></DIV></TD>
                                </TR>

EOF
$x=x+4;
}

print <<EOF;

</TBODY>

                            </TABLE>
<BR>

                        </td>
                    </tr>
                        </table></td>
        </tr>
        <tr>
                <td width="x" nowrap></td>
                <td width="x" nowrap></td>
                <td width="x" nowrap></td>
                <td width="x" nowrap></td>
                <td width="x" nowrap></td>
                <td width="x" nowrap></td>
                <td width="x" nowrap></td>
                </tr>
</table><p>&nbsp;</p>

</body>
</html>
EOF

    [download]
[reply]
[d/l]

      Eeek. For one, unless you really, really trust your users to type 

        state = 'CA'

      [download]
      into the form, you're asking for trouble. Google for "SQL injection attack" or use super search here.

      Far better is to do something like 

        my $sql = "SELECT city FROM table WHERE state = ?";
  my $sth = $dbh->prepare($sql);
  $dbh->execute($FORM{state});

      [download]
      By doing this, the value will be quoted automagically as appropriate for MySQL, elimating the possibility that someone will type 
        1; drop table database

      [download]
      into the form and have the results ruin your day.

      Then you have to read the results before finishing the statement handle and disconnecting.

      Also, since you're using CGI, it is completely unecessary to attempt to disect the query string yourself (and dissecting query strings by hand can be trickier than it first appears). CGI is quite good at handling that for you. You can drop that entire chunk of code that sets up %FORM, and instead do 

        use CGI;

  my $cgi = CGI->new();
  ...
  my $sth = $dbh->prepare($sql);
  $sth->execute($cgi->param('state')) or die ...

      [download]
[reply]
[d/l]
[select]
      Well, there are two things wrong with your prepare/execute. First of all, putting $dbh->errstr inside of a double-quoted string doesn't do what you want it to (it stringifies $dbh, and then it just keeps "->errstr" as part of the literal string). That should be: 
      ... or die "Couldn't execute query: ".$dbh->errstr;

      [download]
      So that you can get some useful debugging information from the database.

      Second (just to take a wild guess here), your query string looks something like "...&state=TX&..." which means that the way you need to generate your sql statement is more like: 

      $statement = "SELECT city FROM database WHERE state='$FORM{state}'";

      [download]
      Of course, this would be very BAD because you're not stripping single-quotes from your state, and besides, queries should use bound placeholders (in general). So what it should actually be is: 
      $statement = "SELECT city FROM database WHERE state=?";

$sth = $dbh->prepare($statement) or die "Couldn't prepare the query: "
+.$sth->errstr;

$rv = $sth->execute($FORM{state}) or die "Couldn't execute query: ".$d
+bh->errstr;

      [download]
      If that's over your head, the short answer is that that question-mark in the query works kind of like a %d would in printf. Anyway, you should read the DBI docs about placeholders (also called "bind variables" or "binds") for more info.

      Oops... see UPDATE comment below.

      Last, you're actually going to want to fetch the data from that query and do something with it. That will probably look something like (and there are many ways to do this... but here's one way): 

      # inside the html <table>
foreach my $row ($sth->fetchall_arrayref) {
   my $state = $row->[0];
   print "... some html or other based on $state...";
}

      [download]

      Anyway, it appears that you're gonna want to do some documentation reading and possibly looking at more example code.

      Oh... and I didn't bother to look any further down than the query, because that is clearly going wrong right now... you need to resolve this problem before you worry about how you the html in exactly whatever way you want. Learn to walk before you learn to run, and all.

      Good luck.

      P.S. Oh, and I'll leave the rant about how you should be using CGI.pm to someone else :-)

      UPDATE: Actually, I made a mistake by not looking closely enough all the way to the bottom... you are fetching the results (which is good). BUT since you are calling $sth->finish and $dbh->disconnect at the top of the script and doing your fetching at the bottom of the script (after finishing and disconnecting), that's gonna break. (Also, this is why I missed the fetching down below... I assumed (like the code would) that once you have disconnect from the DB, you're not doing any further calls against the DB.) What this is means is: move the finish and disconnect to the bottom after you have fetched. 

      ------------
:Wq
Not an editor command: Wq

      [download]
[reply]
[d/l]
[select]
      The important answers on how to use DBI and CGI are above. I'd like to add just a small hint. Separate your code from html using a template-tool like HTML::Template or the Template Toolkit makes it much easier to maintain your script in the future.
[reply]

Back to Seekers of Perl Wisdom