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

Hello:

I want to get all the values in a set. There are two values in this set, and my code only gets one value instead of two.

Heres my code:
&account_verify;
use DBI;

 my $dbh = DBI->connect('DBI:mysql:MAIL') or die "Couldn't connect to 
+database: " . DBI->errstr;


     $sql = "SELECT DISTINCT date FROM mailserve";

$sth = $dbh->prepare($sql) or print "preparing: ",$dbh->errstr;
$sth->execute or print "executing: ", $dbh->errstr;
$row = $sth->fetchrow_hashref;
  $dbh->disconnect;

########################


&header;
print <<EOF;
<B><u>To Do Date Reports</u>:</B><BR><BR>

EOF
print <<EOF;

<a href="/mailtodo.pl?username=$username&session=$session&action=viewd
+ate&date=$row->{'date'}">$row->{'date'}</a>
EOF
while (@rowxx = 
    $sth->fetchrow_array())  # keep fetching until 
                             # there's nothing left
{
print <<EOF;
<a href="//mailtodo.pl?username=$username&session=$session&action=view
+date&date=$rowxx[1]">$rowxx[1]</a>
EOF
}
print <<EOF;
</table>
<br>
EOF
$sth->finish;
&footer;

[download]


I don't kno why it doesn't get both values. Anyone have a idea what I'm diong wrong?

Anthony

Replies are listed 'Best First'.
Re: DBI fetch array
by FamousLongAgo (Friar) on Oct 21, 2002 at 02:54 UTC
    You seem to be getting one row from your result set, closing the database handle, and printing out the first value retrieved.
    The while loop doesn't do anything, because you are dealing with a closed database handle. Moreover, even if you moved the disconnect statement to the end of your code, you are fetching the second element of an array ( $rowxx[1] ) that contains only one element ( $rowxx[0], e.g., the date ). Remember that array indices start at 0.
    I would urge you to consider separating the display and data retrieval bits of your code, and using some kind of indentation style to make things easier to read.
[reply]
[d/l]
[select]
Re: DBI fetch array
by grep (Monsignor) on Oct 21, 2002 at 02:48 UTC

    You're missing some code that we need to see. You showed the SQL for retreiving the date, but I don't see anything where you're setting up the statement handle for your fetchrow_array.

    You disconnected your database handle after you retreive the date and never do anything else with it

    If you are expecting to do the fetchrow_array from 'SELECT DISTINCT date FROM mailserve', your DISTINCT will only retreive one row, but that doesn't matter since you destory your database handle (which destroys your statement handle $sth). You are also only retrieving 1 value (date) which would be $rowxx[0] not $rowxx[1].

    grep
    Mynd you, mønk bites Kan be pretti nasti...

[reply]
[d/l]
[select]
Re: DBI fetch array
by busunsl (Vicar) on Oct 21, 2002 at 08:42 UTC
    From perldoc DBI (highlighting by me):

    $hash_ref = $sth->fetchrow_hashref;

An alternative to `fetchrow_arrayref'. Fetches the 
**next row**
of data and returns it as a reference to a hash
containing field name and field value pairs.  Null
fields are returned as `undef' values in the hash.

    [download]
    To fetch all rows, use $dbh->selectall_arrayref.

    Or in your case, where there is only one column: 

    my @rows = @{$dbh->selectcol_arrayref($sql)};

    [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom