t-strong has asked for the wisdom of the Perl Monks concerning the following question:

In my single query to MySQL DB through Mysql.pm I get 2D-array. I need to reach every element of it. Here is what I'm doing:

while(
@row=$dbh->Query("select s.sect, s.test, t.name, t.res_tbl t.f_num fro
+m s_lab_recs s, tests t where s.s_lab_id=$id and s.sect=t.sect_id")->
+FetchRow
){
  @cont=\@row;
  push @sect, $cont[0];
  push @test, $cont[1];
}

[download]

But the cycle is eternal. Why? And am I correct in retrieving the elements?

Code tags added by GrandFather

Replies are listed 'Best First'.
Re: How to reach each element of 2d-array retrieved from Mysql query?
by davorg (Chancellor) on May 25, 2006 at 07:54 UTC

    It's hard to see what you're doing as you didn't put any code tags in you post. But you are using Mysql.pm which is only there for backwards compatibility. You should really look at using DBI and DBD::mysql instead (see here for more information.)

    --
    <http://dave.org.uk>

    "The first rule of Perl club is you do not talk about Perl club."
    -- Chip Salzenberg

[reply]
      I know that, thank You! But my problem remains: I got to use Mysql.pm and need to retrieve all the rows I get from the query instead just one. Please, tell me how to do it.
[reply]

        I don't know anything about Mysql.pm, so all this comes from a quick scan of the documentation, but it looks to me like you are executing the statement and fetching the first row each time you go round the loop. I think you need to call query before you go into the loop.

        And when you get the loop working correctly, you can simplify all of the code within it to:

        push @sect, \@row;

        [download]
        --
        <http://dave.org.uk>

        "The first rule of Perl club is you do not talk about Perl club."
        -- Chip Salzenberg

[reply]
[d/l]
Re: How to reach each element of 2d-array retrieved from Mysql query?
by ruzam (Curate) on May 25, 2006 at 15:40 UTC
    You're performing the query and the fetch in a single step. Every time the loop executes you repeat the query and the fetch starts back at the beginning.

    You have to execute the query outside the loop, then execute the FetchRow within the loop 
    my $q = $dbh->Query("select s.sect, s.test, t.name, t.res_tbl t.f_num 
+fro
+m s_lab_recs s, tests t where s.s_lab_id=$id and s.sect=t.sect_id");

while(my @row = $q->FetchRow) {
    push @sect, $row[0];
    push @test, $row[1];
}

    [download]
[reply]
[d/l]
      Thank You very much, ruzam! It worked!
[reply]

Back to Seekers of Perl Wisdom