Date sent: Fri, 26 Jan 2001 07:49:33 -0500
To: [EMAIL PROTECTED]
From: James Ervin <[EMAIL PROTECTED]>
Subject: Go through result set twice?
> Dear DBI list:
>
> I need to iterate through a result set twice. Right now I am running the
> query twice, but it is really slow. Here is an example of what I am trying
> to do:
>
> $sth=$dbh->prepare($sql);
> $rv = $sth->execute || die "cant't execute the query: \n $sql \n:
> $sth->errstr";
> $rv = $sth->rows;
> while (@row = $sth->fetchrow_array) {
> Do something for each row;
> }
> while (@row = $sth->fetchrow_array) {
> Do something else for each row;
> }
>
> However, when I get to the second while loop, I can no longer use the
> fetchrow_array method. Is there a way that I can copy the data set so that
> I can run through it again?
>
You can fetch all your rows at a time saying:
$arrayref = $dbh->selectall_arrayref($sql);
$arrayref is then a reference to an array holding references to all fetched
rows. You may iterate over all fetched rows like this as many times as you
want:
foreach $reference (@$arrayref) {
do something with $reference like
dereferencing it: @row = @$reference
or addressing items directly: $reference->[index] ...
}
Also see perldoc DBI.
Bodo
[EMAIL PROTECTED]
Dr. med. Bodo Eing
Institut fuer Medizinische Mikrobiologie
Klinische Virologie
v.-Stauffenbergstr. 36
48151 Muenster
Germany
Phone: ++49 251 7793 111 Fax: ++49 251 7793-104
