DBI Output Help

[Tom]

Hello.

I am learning Perl and have decided to create a web page that has
dropdowns populated by a postgreSQL DB. I've been able to do this and
the page looks fine but the code is awful. A chunk of the code is
posted below; can anyone suggest a way to clean this up and make it
more efficient? Namely, I am trying to consolidate the actual
population of the <SELECT> into one loop, and figure out a way to get
rid of the ugly "$i < scalar(@array)" line.

This all works, but I'm still learning Perl and haven't figured out
how to make it work good!

Thanks,
Tom

CODE BELOW:
===========


#!/opt/perl/bin/perl -wT
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);

my $q = new CGI;

my $dbh = DBI->connect( "dbig:dbname=web" ) || die;
my @a = ('groupdescription','groups');
my @b = ('role','servers_role');
my $sth1 = $dbh->prepare("Select $a[0] from $a[1] order by $a[0]");
my $sth2 = $dbh->prepare("Select $b[0] from $b[1] order by $b[0]");

$sth1->execute;
$sth2->execute;

print $q->header,
$q->start_html,
$q->startform,
"<table><Tr><td>$a[0]</td>",
"<td><Select name=\"$a[0]\">";

while( @row1 = $sth1->fetchrow ) {
for( $i = 0; $i < scalar(@row1); $i++ ) {
print "<option value=\"$row1[$i]\">$row1[$i]";
}
}
print "</Select></td></Tr>",
"<Tr><td>$b[0]</td>",
"<td><Select name=\"$b[0]\">";

while( @row2 = $sth2->fetchrow ) {
for( $i = 0; $i < scalar(@row2); $i++ ) {
print "<option value=\"$row2[$i]\">$row2[$i]";
}
}
print "</Select></td></Tr></table></body></html>";

 

[Michael Budash]

Hello.

I am learning Perl and have decided to create a web page that has
dropdowns populated by a postgreSQL DB. I've been able to do this and
the page looks fine but the code is awful. A chunk of the code is
posted below; can anyone suggest a way to clean this up and make it
more efficient? Namely, I am trying to consolidate the actual
population of the <SELECT> into one loop, and figure out a way to get
rid of the ugly "$i < scalar(@array)" line.

This all works, but I'm still learning Perl and haven't figured out
how to make it work good!

Thanks,
Tom

CODE BELOW:
===========


#!/opt/perl/bin/perl -wT

use strict;

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

my $q = new CGI;
..
..
..
while( @row1 = $sth1->fetchrow ) {
for( $i = 0; $i < scalar(@row1); $i++ ) {
print "<option value=\"$row1[$i]\">$row1[$i]";
}
}

while( my @row1 = $sth1->fetchrow_array ) {
print qq|<option value="$row1[0]">$row1[0]|;
}

..
..
..


hth-

 

[Tom]

Hello.

I am learning Perl and have decided to create a web page that has
dropdowns populated by a postgreSQL DB. I've been able to do this and
the page looks fine but the code is awful. A chunk of the code is
posted below; can anyone suggest a way to clean this up and make it
more efficient? Namely, I am trying to consolidate the actual
population of the <SELECT> into one loop, and figure out a way to get
rid of the ugly "$i < scalar(@array)" line.

This all works, but I'm still learning Perl and haven't figured out
how to make it work good!

Thanks,
Tom

CODE BELOW:
===========


#!/opt/perl/bin/perl -wT
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);

my $q = new CGI;

my $dbh = DBI->connect( "dbig:dbname=web" ) || die;
my @a = ('groupdescription','groups');
my @b = ('role','servers_role');
my $sth1 = $dbh->prepare("Select $a[0] from $a[1] order by $a[0]");
my $sth2 = $dbh->prepare("Select $b[0] from $b[1] order by $b[0]");

$sth1->execute;
$sth2->execute;

print $q->header,
$q->start_html,
$q->startform,
"<table><Tr><td>$a[0]</td>",
"<td><Select name=\"$a[0]\">";

while( @row1 = $sth1->fetchrow ) {
for( $i = 0; $i < scalar(@row1); $i++ ) {
print "<option value=\"$row1[$i]\">$row1[$i]";
}
}
print "</Select></td></Tr>",
"<Tr><td>$b[0]</td>",
"<td><Select name=\"$b[0]\">";

while( @row2 = $sth2->fetchrow ) {
for( $i = 0; $i < scalar(@row2); $i++ ) {
print "<option value=\"$row2[$i]\">$row2[$i]";
}
}
print "</Select></td></Tr></table></body></html>";

Try this...

my ($selectrow1,$selectrow2) =
("<table><Tr><td>$a[0]</td><td><select name='$a[0]'>",
"<tr><td>$b[0]</td><td><select name='$b[0]'>");

while( (@row1 = $sth1->fetchrow ) or (@row2 = $sth2->fetchrow) )
{
for (@row1) { $selectrow1 .= "<option value='$_\'>$_</option>\n" }
for (@row2) { $selectrow2 .= "<option value='$_\'>$_</option>\n" }
}
print "$selectrow1</Select></td></Tr>\n$selectrow2</Select></td></Tr></table></body></html>";


Tom
ztml.com

 

[Tom]

Hello.

I am learning Perl and have decided to create a web page that has
dropdowns populated by a postgreSQL DB. I've been able to do this and
the page looks fine but the code is awful. A chunk of the code is
posted below; can anyone suggest a way to clean this up and make it
more efficient? Namely, I am trying to consolidate the actual
population of the <SELECT> into one loop, and figure out a way to get
rid of the ugly "$i < scalar(@array)" line.

This all works, but I'm still learning Perl and haven't figured out
how to make it work good!

Thanks,
Tom

CODE BELOW:
===========


#!/opt/perl/bin/perl -wT
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);

my $q = new CGI;

my $dbh = DBI->connect( "dbig:dbname=web" ) || die;
my @a = ('groupdescription','groups');
my @b = ('role','servers_role');
my $sth1 = $dbh->prepare("Select $a[0] from $a[1] order by $a[0]");
my $sth2 = $dbh->prepare("Select $b[0] from $b[1] order by $b[0]");

$sth1->execute;
$sth2->execute;

print $q->header,
$q->start_html,
$q->startform,
"<table><Tr><td>$a[0]</td>",
"<td><Select name=\"$a[0]\">";

while( @row1 = $sth1->fetchrow ) {
for( $i = 0; $i < scalar(@row1); $i++ ) {
print "<option value=\"$row1[$i]\">$row1[$i]";
}
}
print "</Select></td></Tr>",
"<Tr><td>$b[0]</td>",
"<td><Select name=\"$b[0]\">";

while( @row2 = $sth2->fetchrow ) {
for( $i = 0; $i < scalar(@row2); $i++ ) {
print "<option value=\"$row2[$i]\">$row2[$i]";
}
}
print "</Select></td></Tr></table></body></html>";

Try this...

my ($selectrow1,$selectrow2) =
("<table><Tr><td>$a[0]</td><td><select name='$a[0]'>",
"<tr><td>$b[0]</td><td><select name='$b[0]'>");

while( (@row1 = $sth1->fetchrow ) or (@row2 = $sth2->fetchrow) )
{
for (@row1) { $selectrow1 .= "<option value='$_\'>$_</option>\n" }
for (@row2) { $selectrow2 .= "<option value='$_\'>$_</option>\n" }
}
print "$selectrow1</Select></td></Tr>\n$selectrow2</Select></td></Tr></table></body></html>";


Tom
ztml.com

Thanks a lot for the help guys! I ended up with a solution very
similar to your suggestions:

while( my @row = $sth1->fetchrow ) {
for(@row) { print "<option value='$_'>$_</option>" }
}

Works great. I also am using 'fetchrow_array' to retrieve single
results; and yes, I put use strict; back into place ;-).

Thanks again,
Tom