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

dear monks, I cant make a foreach loop iterate over a list more than once. I pull the db data fine and join it into a list. It fetches the first row of data and then terminates??? this is what i have: 
#!perl -wT


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

my $q = new CGI;


my %attr        =    (RaiseError=>1, PrintError=>1);
my @field_name    = qw ( property_id address_1 address_2 county city z
+ip description status bedroom bathroom );
my @field_desc    = qw ( ID Address Address County City PostalCode Des
+cription Status Bedrooms Bathrooms );
my $dbh    = DBI->connect("dbi:mysqlPP:$db:$server", $username, $passw
+ord,{RaiseError =>1});
print    $q->header("text/html"),
        $q->start_html(-title=>"Lettingsetc.co.uk | Admin | Edit", -sr
+c=>"/mycss.css"),
        $q->h1("Lettingsetc.co.uk"),
        $q->h4("Administration>Edit property details!");

my $field_list = join (",", @field_name);

print <<HTML;
<H1>All Properties</H1>
<TABLE BORDER=1>
<TR>
HTML

my $SQL = "SELECT $field_list FROM property";

my $i;
foreach  $i(@field_desc) {
    print "<TH>$i</TH>\n";
}
print qq `</TR><TR>`;

my $sth = $dbh->prepare($SQL);
$sth->execute() or die $dbh->errstr;

my $row;
foreach $row( $sth->fetchrow_array)# while more rows fetch next one
{
  print "<TD>$row</TD>\n";# print row data in HTML 
}
print "</TR></TABLE>";
print $q->end_html;

$dbh->disconnect() or die $dbh->errstr;

[download]
Thanks

Replies are listed 'Best First'.
Re: foreach loop
by pfaut (Priest) on Jan 20, 2003 at 18:26 UTC

    This code gets very hard to follow because you keep jumping back and forth between generating HTML and building and executing an SQL query. Try to do one thing at a time and progress to the next thing that needs to be done. It will make your code much easier to follow and debug.

    Your program looks fine until the foreach loop. You are asking for a row of data to be returned as an array of values but you assign the result to a single scalar. As a result, you'll only get one value back for each call to fetchrow_array. To fix this, either receive the values into an array or use fetchrow_arrayref to get a reference to an array of values.

    The other problem is inside the foreach. You need nested loops - one to iterate through the rows and an inner loop to iterate through the values in the row.

    The second half of your program should look something like this.

    my $i;
# output column headers
foreach  $i(@field_desc) {
    print "<TH>$i</TH>\n";
}
print qq `</TR>`;

my $sth = $dbh->prepare($SQL);
$sth->execute() or die $dbh->errstr;

my $row;
foreach $row ( $sth->fetchrow_arrayref )# while more rows fetch next o
+ne
{
  print "<TR>";
  foreach my $cell (@$row) {
    print "<td>$cell</td>";
  }
  print "</TR>";
}
print "</TABLE>";
print $q->end_html;

$dbh->disconnect() or die $dbh->errstr;

    [download]

    You might also consider using CGI methods to generate your html. The foreach loop could be reduced to this.

    foreach $row ($sth->fetchrow_arrayref) {
  print Tr(td($row));
}

    [download]
    ---
print map { my ($m)=1<<hex($_)&11?' ':'';
$m.=substr('AHJPacehklnorstu',hex($_),1) }
split //,'2fde0abe76c36c914586c';

    [download]
[reply]
[d/l]
[select]
Re: foreach loop
by poj (Abbot) on Jan 20, 2003 at 18:45 UTC
    You need to repeat the ->fetchrow_array for each row until you get an empty list. Something like this (untested)
    ..

my $i;
foreach $i (@field_desc) {
    print "<TH>$i</TH>\n";
}
print qq "</TR>";


my $SQL = "SELECT $field_list FROM property";
my $sth = $dbh->prepare($SQL);
$sth->execute() or die $dbh->errstr;
my @row;
while ( @row = $sth->fetchrow_array() )
{
  print qq(<tr>);
  for (@row){
    print qq(<td>$_</td>);
  }
  print qq(</tr>);
}

print "</TABLE>";
print $q->end_html;

$dbh->disconnect() or die $dbh->errstr;

    [download]
    poj
[reply]
[d/l]
Re: foreach loop
by hardburn (Abbot) on Jan 20, 2003 at 18:25 UTC

    Don't mix HTML with actual programming. Make a subroutine that builds the data fetched from the database into an array. Then have another subroutine that builds the HTML from that array. Better yet, use HTML::Template. I'm not entirely sure what your code's problem is, but I suspect decupling your data from code will magically make it go away.

[reply]

Back to Seekers of Perl Wisdom