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?

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.