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( "dbi:Pg: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]

In article <f4144fab.0309061156.77e83ec1@posting.google.com >,
[email]ompers@hotmail.com[/email] (Tom) wrote:

> 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-

--
Michael Budash

[Tom]

[email]ompers@hotmail.com[/email] (Tom) wrote in message news:<f4144fab.0309061156.77e83ec1@posting.google. com>...

> 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( "dbi:Pg: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]

[email]tom@ztml.com[/email] (Tom) wrote in message news:<59b4279a.0309070513.344d894e@posting.google. com>...

> [email]ompers@hotmail.com[/email] (Tom) wrote in message news:<f4144fab.0309061156.77e83ec1@posting.google. com>...

> > 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( "dbi:Pg: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