Retrieving multiple values from a Database 1

[KalahariFerrari]

Hi there,
I'm new to perl and want to find out how to retrieve multiple entries that I get from a database into an array, and then how to get values from the array.
I can can get a single row into the array, but i'm not sure how to get multiple rows into the array if this is returned by my querry.

Thnx in advance

Tommy

 

[audiopro]

What database are you using and how are you processing the results of your query?

Keith

 

[raklet]

One way to do it is to use DBI's fetchrow_array method. This fetches the next row of data and returns it as a list containing the field values. So, say you had a db with three columns (person, id, value), and you did a select * from db. Using the fetchrow_array method, all three columns would be returned as a list. You can either store this list to scalar variables:

my ($person, $id, $value ) = $select->fetchrow_array;

or just stash it in an array:

my @row_values = $select->fetchrow_array;

Mind you that this only takes care of one row. If there are multiple rows returned, then you have to take each individual row and push it into another array - thus creating an array of arrays.

my @all_rows;
while (something) {
    [...]
    my @row_values = $select->fetchrow_array;
    push @all_rows, [@row_values];
    [...]
}

You now have an array of arrays in @all_rows that might look something like this:

(
    [person, id, value] # index 0 - contains an array with three values
    [person, id, value] # index 1 - contains an array with three values
    [person, id, value] # index 2 - contains an array with three values
)

To loop through the array and print all of the values out, you would do something like this:

foreach my $row (@all_rows) {
    foreach (@{$row}) {
        print "$_\n";
    }
    print "Next row\n";
}

Hth,

Raklet

 

[KalahariFerrari]

Oracle 8i, I'm stripping the results from the array, as at the moment it is only one entry, and fairly easy to do.

Tom

 

[mbrooks]

You now have an array of arrays in @all_rows that might look something like this:

You can do this action easier by using fetchall_arrayref({})

my $result_ref = $sth->fetchall_arrayref({});

foreach $col ( @$result_ref ) {
	print "$col->{person} / $col->{id} / $col->{value}\n";
}

M. Brooks

http://mbrooks.info

 

[KalahariFerrari]

Ho do i select info from the array now.eg. If I want to use certain info in another querry, how do I get that from the array.

 

[raklet]

You have to loop through the array looking for the info you want. Something like this:

foreach $col ( @$result_ref ) {
    print "$col->{person} / $col->{id} / $col->{value}\n";
    if ($col->{person} eq "something") {
        do this
    }
}

 

[raklet]

You have to loop through the array looking for the info you want. Something like this:

foreach $col ( @$result_ref ) {
    print "$col->{person} / $col->{id} / $col->{value}\n";
    if ($col->{person} eq "something") {
        do this
    }
}