Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

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:


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.


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 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 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 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']);
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 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?
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?

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://449914]
Approved by polettix
[marto]: This won't take till Summer to complete I hope ;)
[Discipulus]: i hope too, vacuum cleaner permitting, 2-3 afternoon to build. Or is the 1:1 serie? ;=)
[Tanktalus]: Sure, no one is active for hours and hours in the cb, so I go to upgrade the db, and then someone is active. Sheesh :)
[Discipulus]: anyway poppins probably died with the last night cold. But is not normal to see them in dec. they must pop in April
NodeReaper eyes the thorns in the side
Discipulus : the party puller!
[Discipulus]: I was trying to solve this but i'm not. my regex-fu is stuck at primary school

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (6)
As of 2017-12-18 21:35 GMT
Find Nodes?
    Voting Booth?
    What programming language do you hate the most?

    Results (498 votes). Check out past polls.