Beefy Boxes and Bandwidth Generously Provided by pair Networks Bob
Just another Perl shrine
 
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 musing on the Monastery: (10)
As of 2014-04-16 06:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (416 votes), past polls