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

I am trying to create a scrolling list with data selected from a mysql database. When it works it prints the 1s all over the page. How do I reference the data from an array populated by fetchrow_array?

Replies are listed 'Best First'.
Re: scrolling list
by Chmrr (Vicar) on Dec 21, 2001 at 01:36 UTC

    I'm afraid you'll need to be a bit more specific -- perhaps provide some code? fetchrow_array by itself just returns a perfectly normal array. Thus:

    my $sth = $dbh->prepare("SELECT foo,bar FROM baz");
$sth->execute;
while (my @row = $sth->fetchrow_array) {
  print join "\t", @row;
  print "\n";
}

    [download]

    By "scrolling list," do you mean some sort of CGI form? A Tk widget? Give us a little more to go on..

    Update: Applying judicious use of the PSI::ESP module, I'm assuming:

    • You want an HTML scrolling list
    • You're using CGI.pm (which should be a given if the first is true)
    • Your query only returns one column, and you want that whole column in the scrolling list
    my $sth = $dbh->prepare("SELECT foo,bar FROM baz");
$sth->execute;
my @data;
while (my @row = $sth->fetchrow_array) {
  push @data, $row[0];
}
print $query->scrolling_list(-name   => 'list_name',
                             -values => [@data]);

    [download]

    Further reading:

    perl -pe '"I lo*`+$^X$\"$]!$/"=~m%(.*)%s;$_=$1;y^`+*^e v^#$&V"+@( NO CARRIER'

[reply]
[d/l]
[select]
Re: scrolling list
by boo_radley (Parson) on Dec 21, 2001 at 01:59 UTC
    That really depends. Are you trying to do this in a cgi form, tk, wx, win32::GUI, or ...?
    but, your question's really about getting information from fetchrow_array; here's a short script to get you started. 
    use DBI;
my $table_name = "perl";
my $db_name = "system";
my $user_id = "doc";
my $password ="your password here";
my $interface = "DBI";
my $row = ( "$db_name ('$table_name$user_id $interface')");
eval $row;

    [download]
[reply]
[d/l]
Re: scrolling list
by talexb (Chancellor) on Dec 21, 2001 at 01:37 UTC
    I suggest you check this out first, and come back if you have any more questions.

    "Excellent. Release the hounds." -- Monty Burns.

[reply]
Re: scrolling list
by thor (Priest) on Dec 21, 2001 at 18:33 UTC
    Quote:
    <quote> my @data = $sth->fetchrow_array;

    Then you have a nice array with all the elements of your sql return. Then just setup the select and itterate through this array like so: </quote>

     My understanding of that is that @data now has one row of the select not the entire select (hence the name fetchrow).
    <br< I think the problem is that you're only fetching a row once.

    Here is some (untested) code: 
    my $sth = $dbh->prepare(select * from table) or die "$DBI::errstr\n";
$sth->execute() or die "$DBI::errstr\n";
while (my @array = $sth->fetchrow_array)
 {
 #do stuff with @array;
 }

    [download]
    This will grab all of the rows in the select.
[reply]
[d/l]
[select]
Another simple answer by tradez
by tradez (Pilgrim) on Dec 21, 2001 at 02:57 UTC
    I think I have done what you are trying to do a million times, tell me if this is what you are looking for. Basically first do the assumed code to get the array: 
    my $sth = $dbh->prepare("SELECT stuff FROM table");
$sth->execute;
my @data = $sth->fetchrow_array;

    [download]
    Then you have a nice array with all the elements of your sql return. Then just setup the select and itterate through this array like so: 
    print "<select name = 'test'>";
foreach my $entry ( @data ) {
  print "<option value = '$entry'>$entry</option>";
}
print "</select>";

    [download]
    And if what is what you are looking for, then there is a simple answer, if not, BUGGER OFF! Tradez
[reply]
[d/l]
[select]
      If you have done this a million times, I am afraid that you could end up with a million errors.
      Your statement my @data = $sth->fetchrow_array; will only fetch the first row from the result set. After which, if you don't call $sth->finish(), you'll end up with some memory being allocated and unreleased.
      Check the above answer by Chmrr for a correct use of fetchrow_array with while.

      BTW, your comment that the enquiring monk should B... OFF is not going to do much good for his problem.
       _  _ _  _  
(_|| | |(_|><
 _|
      For every complex problem, there is a solution that is simple, neat, and wrong
      H. L. Mencken
[reply]
[d/l]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom