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

Hi,

I want to execute a query and safe the data in an array or something so i can retrieve the data later. This is the code i already have:

my $sth = $dbh->("SELECT name, adres, woonplaats from personen";

$sth ->execute();

my $array_ref = $sth->fetchall_arrayref();

foreach my $row(@$array_ref){
my( $name, $adres, $woonplaats) = @$row;
print "$name t $adres t $woonplaats";
}

[download]
How is the data being saved and how can i acces that data? For example row by row.

Hope someone can help.

Thanks.

Updated Steve_p - added code tags

Replies are listed 'Best First'.
Re: fetchall( )
by polettix (Vicar) on Apr 21, 2005 at 10:51 UTC
    Did you try to complile it (perl -c filename.pl)? Probably you should fix syntax errors before going to the next stage.
    • in the SELECT row, you should call the prepare statement and close the parenthesis;
    • checking the error results from the calls would be fine;
    • did you successfully initiated the connection towards the database, i.e. is $dbh valid?
    Regarding your particular question, the last part of your code prints out the selected data: you can use $array_ref wherever you need it, as long as this variable doesn't go out of scope. So, the way you're accessing the data seems correct to me.

    As a terminating node, I've been hand-slapped yesterday for using a one-word title in a post: don't do that, and the admins will be happy. Suggested readings: How do I compose an effective node title? and Writeup Formatting Tips (your post lacks code tags).

    Flavio (perl -e "print(scalar(reverse('ti.xittelop@oivalf')))")

    Don't fool yourself.
[reply]
[d/l]
[select]
Re: fetchall( )
by RatArsed (Monk) on Apr 21, 2005 at 12:17 UTC

    You've actually answered your own question; You have saved the results in an array refererence. And you've shown how to iterate through it with your foreach() loop. Excusing typos

    # $dbh has already been ->connect()ed
my $sth = $dbh->prepare( "SELECT name, adres, woonplaats FROM personen
+" );

$sth ->execute();

# Get all the results rows and store the array reference
my $array_ref = $sth->fetchall_arrayref();

# For each row in the result set, print it out.
foreach my $row(@$array_ref)
{
   my( $name, $adres, $woonplaats) = @$row;
   print "$name t $adres t $woonplaats";
}

    [download]

    Personally, I'm not a fan of fetchall(), and would do it more like:

    # $dbh has already been ->connect()ed
my $sth = $dbh->prepare( "SELECT name, adres, woonplaats FROM personen
+" );

$sth ->execute();

# For each row in the result set, print it out.
while( my( $name, $adres, $woonplaats ) = $sth->fetchrow_array )
{
   print "$name t $adres t $woonplaats";
}

    [download]

    Caveat: This won't hold on to the data if you want to iterate over each row more than once

    --
    RatArsed

[reply]
[d/l]
[select]
Re: fetchall( )
by pearlie (Sexton) on Apr 21, 2005 at 10:45 UTC
    Hello, You need to store the data retrieved in an array of arrays, where each array in the bigger array represents a row. After retrieving each row, push it into the bigger array. Then you can use Data::Dumper module to display the data later.
[reply]
      I'm new to perl, so can you tell me how to do that? Or give me a few lines of sample code?
[reply]
        The Data::Dumper module is a great way to see what your data structure is like. To print the entire array structure: 
        use Data::Dumper;

my $ref = $sth->fetchall_arrayref();

print Data::Dumper->Dump([$ref], ['*ref']);

        [download]
        -albert
[reply]
[d/l]
Re: fetchall( )
by Transient (Hermit) on Apr 21, 2005 at 11:53 UTC
    I'm not seeing any connection here so I'm assuming you're doing that prior to this.

    Your first line is missing a prepare method and a closing paren.
[reply]
Re: fetchall( )
by maarten_m4 (Initiate) on Apr 21, 2005 at 12:59 UTC
    But i want to save the data all in a array or something. Because i need to compare it with itself. So i need all the data to be safed. So i can acces it later on. Like a 2d array
[reply]
      When you do a fetchall_arrayref, that returns a reference to what is essentially a 2d array. 
      my $ref = $sth->fetchall_arrayref();

gives:

$ref   [
          [row of 1st data in here],
          [2nd row data here],
          [3rd row of data here]
       ]

      [download]
[reply]
[d/l]
        than i would have something like this: row1: name, adress, place row2: name, adress, place row3: name, adress, place And how do i select by example only the name of row 2?
[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom