Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

fetchall( )

by maarten_m4
on Apr 21, 2005 at 10:35 UTC ( #449914=perlquestion: print w/ replies, xml ) Need Help??
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"; }
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

Comment on fetchall( )
Download Code
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.
      I'm new to perl, so can you tell me how to do that? Or give me a few lines of sample code?
        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']);
        -albert
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.
Re: fetchall( )
by maarten_m4 on Apr 21, 2005 at 11:44 UTC
    I'm new to perl, so can you tell me how to do that? Or give me a few lines of sample code?
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.
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"; }

    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"; }

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

Re: fetchall( )
by maarten_m4 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
      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] ]
        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?

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://449914]
Approved by polettix
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (6)
As of 2015-07-04 19:53 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (60 votes), past polls