http://qs1969.pair.com?node_id=464938

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

This code takes all the "Distinct" entries in a DB column and puts them in a drop down select HTML form field.

Here is my code:
#!/usr/local/bin/perl -w

use strict;
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
my $q = CGI->new();

print $q->header();

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

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

my $market_stmt = "SELECT DISTINCT market FROM table ORDER BY market A
+SC";

my $market_sth;

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

$market_sth->execute() or die "Couldn't execute query: ".$DBI::errstr;

my @market  = $market_sth->fetchrow_array; #This is some old code wher
+e I tried to fetch all rows and store in an array, this should be del
+eted, I didn't know what I was doing!  When deleted, nothing prints i
+n the "while" statement below!

print <<EOF;
<select name="market" size="1">
EOF

while (my $market = $market_sth->fetchrow_array) {

   print "<option value=\"$market\">$market</option>" ;
}

print <<EOF;
</select>
EOF

[download]
Originally I had "my @market = $market_sth->fetchrow_array;" in there because I wanted to store the entire DB fetch in an array, then print it. I didn't know what I was doing and this is wrong.

As you can see from my code I ended up using a "while" statement to fetch each row and then print one at a time. So when I delete "my @market = $market_sth->fetchrow_array; ", since it's code that doesn't do anything, my "while ($market = $market_sth->fetchrow_array)" breaks and nothing prints out.

Even when I rename "my @market = $market_sth->fetchrow_array;" to "my @market_crazy_name = $market_sth->fetchrow_array;" nothing changes!

Why doesn't any of the DB fetch print in the HTML drop down box with the "my @market = $market_sth->fetchrow_array;" code removed? What the HECK!

UPDATE:

When I change "while ($market = $market_sth->fetchrow_array)" to "while (@market = $market_sth->fetchrow_array)" and delete "my @market = $market_sth->fetchrow_array;" everything works!.

Now my question is:

Why does this work?
my @market712  = $market_sth->fetchrow_array;
while (my $market = $market_sth->fetchrow_array) {
   print "<option value=\"$market\">$market</option>" ;
}

[download]
And this doesn't
# my @market712  = $market_sth->fetchrow_array;
while (my $market = $market_sth->fetchrow_array) {
   print "<option value=\"$market\">$market</option>" ;
}

[download]
What's so special about assigning the first $market_sth->fetchrow_array to @any_array, that it will later let me fetch each row to a scalar?

Replies are listed 'Best First'.
Re: Mysterious Code "DBI" - Why is this working?
by graff (Chancellor) on Jun 09, 2005 at 03:14 UTC
    Responding to your update: you need to read the DBI man page more carefully ("perldoc DBI"). And/or you need to be more attentive to the "sigils" that you attach to your variable names ("@" for arrays, "%" for hashes, "$" for scalar variables or for a single indexed element within an array or hash).

    Here's a relevant snippet from the man page about "fetchrow_array":

    If called in a scalar context for a statement handle that has more than one column, it is undefined whether the driver will return the value of the first column or the last. So don't do that. Also, in a scalar context, an "undef" is returned if there are no more rows or if an error occurred. That "undef" can't be distinguished from an "undef" returned because the first field value was NULL. For these reasons you should exercise some caution if you use "fetchrow_array" in a scalar context.

    Now, it does seem like your query will return a single value per row, so maybe  while ( my $market = $sth->fetchrow_array ) should work the way you expect. But maybe you shouldn't count on that.

    It would suffice to use parens in the assignment, so that everybody is clear that fetchrow_array is being called in a list context: 

    while ( my ( $market ) = $market_sth->fetchrow_array ) {
   ...
}

    [download]
    The fact that you can get it to work in a scalar context at all is probably not important (except to the extent that this reveals bugs or inconsistencies in your particular DBD module). It's just better not to do it that way.
[reply]
[d/l]
[select]
Re: Mysterious Code "DBI" - Why is this working?
by cees (Curate) on Jun 09, 2005 at 05:07 UTC

    Since you have your answer already, I'll instead give a couple of small suggestions that you might use to simplify your code. Have a look at the selectcol_arrayref method in DBI. It will simplify your code quite a bit

    my $opts = $dbh->selectcol_arrayref(<<"");
  SELECT DISTINCT market
    FROM table
   ORDER BY market ASC

print qq{<select name="market" size="1">};
print map { qq{<option value="$_">$_</option>\n} } @$opts;
print qq{</select>};

    [download]

    Also, this is one of the few cases where the HTML generation utilities in CGI.pm can come in handy:

    print $q->popup_menu(-name => 'market',
                     -size => 1,
                   -values => $opts);

    [download]
[reply]
[d/l]
[select]
Re: Mysterious Code "DBI" - Why is this working?
by hubb0r (Pilgrim) on Jun 09, 2005 at 05:58 UTC
    I think you are looking for: 
    while (my $market = $market_sth->fetchrow_arrayref) {
    print "<option value=\"${market->[0]}\">${market->[0]}</option>" ;
}

    [download]
    or something along those lines. You are returning into a scalar an should this be returning a scalar value from your db call. This will give you back a refernce to a single row of the return for each time through the while. Hence just reference the 0th element of the ar_ref and you'll have what you're looking for.
[reply]
[d/l]

Back to Seekers of Perl Wisdom