Gossamer Forum
Home : General : Perl Programming :

$sth->fetchall_array

Quote Reply
$sth->fetchall_array
For some reason I don't have the option to use fetchall_array - do I have an old DBI?

Here's what perldoc DBI tells me I can use:

@row_ary = $sth->fetchrow_array;
$ary_ref = $sth->fetchrow_arrayref;
$hash_ref = $sth->fetchrow_hashref;

$ary_ref = $sth->fetchall_arrayref;

The query I'm using is SELECT Email FROM Members

What would be the best way to get the values into a hash?


Quote Reply
Re: [RedRum] $sth->fetchall_array In reply to
Dear RedRum,

I don't know why DBI has only these method to get record from Database.

I think the following codes may help

my $hash_ref={};
while (my $href = $sth->fetchrow_hashref){
$hash_ref->{$href-{'MemberID'}}=$href;
}

The $hash_ref variable now contains all records you want.

Cheers,



Cheers,

Dat

Programming and creating plugins and templates
Blog
Quote Reply
Re: [RedRum] $sth->fetchall_array In reply to
Code:
$sqlquery= "SELECT Email FROM Members";

$sth = $dbh->prepare($sqlquery);
$sth->execute;
my $records = $sth->fetchall_arrayref({});
$sth->finish();


for my $record (@$records) {

# fieldvalue = $record->{'fieldname'}

}

Or you can define your variables before hand:

Code:
while ($pointer = $sth->fetchrow_hashref) {
$field_value = $pointer->{'field_name'};

print "blah blah $field_value blah";
}

Read the documentation on the use of fetchall_arrayref({});, though. You'll find that fetchrow_arrayref is a faster way to fetch your data.

Wil

Last edited by:

Wil: Sep 28, 2001, 2:12 AM
Quote Reply
Re: [Wil] $sth->fetchall_array In reply to
Thanks guys

Last edited by:

RedRum: Sep 28, 2001, 3:34 AM
Quote Reply
Re: [Wil] $sth->fetchall_array In reply to
I'm not sure if it's the fastest way but it works. I ended up using:

Code:
my $data = $sth->fetchall_arrayref({});
$sth->finish();

foreach (@$data) {
$res{$_->{Email}} = $_->{Email};
}

$DBH->disconnect();

return %res;

Last edited by:

RedRum: Sep 28, 2001, 1:48 PM
Quote Reply
Re: [RedRum] $sth->fetchall_array In reply to
That's fine, Paul. If it ain't broke don't fix it! Not sure about performance differences. In my last post I did comment on that, from what I have read in the DBI manual. But I'm not sure what speed difference there is, even if it's noticable or not.

Cheers

- wil