loop though query results twice

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

I'm trying to make a diferent worksheet for each company, I don't know ahead of time how many company's it will give. The problem is that the second go around, $sql_query is empty (it says i need to execute it again). The second query takes long enouph that I want to execute it just once and loop through the results several times. (the actual queries are pretty complex I made up simple ones to simplify the post)

my $query1 = $dbh_bob->prepare(qq{

        SELECT company
    FROM namespace.phonebook 
}

my $sql_query = $dbh_bob->prepare(qq{
        SELECT name, birthdate_date, gender, company
    FROM namespace.directory 
}

$sql_query->execute;
$query1->execute;


while (@data1 = $query1->fetchrow_array())
{ 
    my $cur_company = @data[0]
     $worksheet = $excel->addworksheet($cur_company);
    row = 0;
    while (@data2 = $sql_query->fetchrow_array()){ 
    if (@data2[3] eq $cur_company) 
    {
             $worksheet->write_row($row++, 0, \@data); 
    }
}
[download]

Comment on loop though query results twice Download Code

Replies are listed 'Best First'.
Re: loop though query results twice by Corion (Patriarch) on Apr 27, 2009 at 20:28 UTC
Why not push the work of combining stuff into the DB? `my $sth = $dbh_bob->prepare(<<SQL); SELECT name, birthdate_date, gender, company FROM namespace.directory order by company SQL $sth->execute(); my $results = $sth->fetchall_arrayref(Slice=>{}); my $cur_company; my $line; for my $row (@$results) { if ($cur_company ne $row->{company}) { $cur_company = $row->{company}; $worksheet = $excel->addworksheet($cur_company); $line = 0; }; $worksheet->write_row($line++,0,$row); };` [download]	[reply] [d/l]
Re: loop though query results twice by roboticus (Chancellor) on Apr 27, 2009 at 20:31 UTC
pdmonney: If your datasets aren't too large, you might try using `fetchall_array` instead of `fetchrow_array` to get the entire dataset. Then you can iterate over the array as many times as you like without having to talk with the database. ...roboticus	[reply] [d/l] [select]
Re^2: loop though query results twice by pdmonney (Initiate) on Apr 28, 2009 at 12:39 UTC
I looked online and I am having trouble understanding the fetchall_array command, and how it works, can you use it in the example I gave?	[reply]
Re^3: loop though query results twice by Anonymous Monk on Apr 28, 2009 at 13:18 UTC
`my $tbl_ary_ref = $query1->fetchall_arrayref; warn "WHOOPS ", $query1->err if $query1->err ; for my $data ( @{ $tbl_ary_ref } ){ my $cur_company = $$data[0]; $cur_company = $data->[0]; ... } for my $data ( @{ $tbl_ary_ref } ){ my $cur_company = $$data[0]; $cur_company = $data->[0]; ... } for my $data ( @{ $tbl_ary_ref } ){ my $cur_company = $$data[0]; $cur_company = $data->[0]; ... }` [download] references quick reference	[reply] [d/l]
Re: loop though query results twice by ikegami (Patriarch) on Apr 27, 2009 at 20:12 UTC
`execute` it again, or save the results from the first pass. `my @rows; while (my $row = $sth->fetch()) { push @rows, [ @$row ]; ... } for my $row (@rows) { ... }` [download] [ Me bad. I pretty much only read the title ]	[reply] [d/l] [select]