Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much

Search an array ref of array refs

by bittondb (Novice)
on Nov 01, 2001 at 07:00 UTC ( #122486=perlquestion: print w/replies, xml ) Need Help??

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

I'm working with an array ref of array refs. This array is the return values from fetchall_arrayref

Code in use:
my $table = $sth->fetchall_arrayref;

Now, I use this array the build a select box. Then I need to fetch a single "row" from $table based on an initial value (a row id). I come from the school of thought that I should just go back to the DB and fetch the single value, but if I already have the table (in $table), then why make the trip to the DB again? I tried messing with map and an anonymous sub but to no avail. I wanted to avoid using a foreach, which I certainly could use, but not as sexy as map. So, what do I do?

Replies are listed 'Best First'.
Re: Search an array ref of array refs
by Fastolfe (Vicar) on Nov 01, 2001 at 07:08 UTC
    See perllol and perldsc. Maybe you want something like this:
    foreach my $row (@{$table}) { print "columns: ", join(", ", @{$row}), "\n"; }
    Re-reading your post, it sounds like you're wanting to do a little more than just iteration. If 'rowid' is just a column, and you plan on doing many of these look-ups, you can approach this two different ways. If by 'rowid' you mean the order the rows appear in the database (thus the order they're SELECTed out, though I'm not sure this can be relied on), iteration or direct-access (e.g. $table->[$rowid]) is probably OK for you. If you're wanting to truly search, something like this might be what you're after:
    my %search_cache; sub search_for { my $table = shift; my $value = shift; return $search_cache{$value} if exists $search_cache{$value}; foreach my $row (@{$table}) { if ($row->{column} eq $value) { return $search_cache{$value} = $row; } } return $search_cache{$value} = undef; # cache failure }
    You can also consider storing just the index into @{$table} instead of the row itself.
      Yes, 'rowId' (actually domainId) is the first column in the table. Lemme expand on what I'm doing. I pull the $table, and then strip the first two columns into a hash, and the first column into a seperate array for the CGI::Form->query->popup_menu method:

      my %labels = map { @$_->[0] => @$_->[1] } @$table; my $values = [map ( $_->[0] , @$table )];

      Now I need to be able to say, give me the "row" from $table that has a domainId of, say 1. Then I can use the values from that row to fill textboxes for the selected domainId. The search code is great. I was just wondering if there was a way to do it w/ map to avoid the looping code. :)
        @found = grep { $_->[$column] == $searching_for } @$table;
Re: Search an array ref of array refs
by jeroenes (Priest) on Nov 01, 2001 at 11:47 UTC
    I personally would follow your first hunch and fetch the row from DBI. Why? Databases are optimized for searching the tables, perl is not. SQL is more clear for searching. Just compare the other solutions with a simple SQL query:  select * from table where id=$rowid;.

    Another point, the databases mostly scale very well. If your table exceeds let's say more than 1000 items, perl will seriously slow down.

    The performance penalty of the additional DBI call moreover probably is pretty small.

    "We are not alone"(FZ)

      Granted, the search is much more efficient in the DB, but I already have the whole table because I just used it to create a SELECT box on my web page. So the whole genesis behind this was to avoid another round trip to the DB, which IMHO, is more costly (but I could be wrong). :)
        What are you optimizing than? Execution speedup cq load time? Are you sure that it takes significantly more time to do an additional query to the server? I guess the connection is not closed between the statement. Maybe the DB is on the same box as the webserver.

        And even if it takes some additional timeframe (probably very small, test it!) for the query, is it worth the worse maintainability of perl code vs the SQL code?

        I generally would answer 'no', but your situation may be different. As ppl say: only optimize the code that needs optimization. Optimization leads to less quality code, so be sure you need it. Just consider what you are doing.

Re: Search an array ref of array refs
by andye (Curate) on Nov 01, 2001 at 17:55 UTC
        No probs. fetchall_hashref() is new, so you might need to update your DBI, and maybe DBD, modules.


Re: Search an array ref of array refs
by bittondb (Novice) on Nov 01, 2001 at 08:22 UTC
    Thanks to the good 'ol folks at google and Perl Limericks:

    my $domain_ip; map { if($_->[0] == $domainid){ $domain_ip = @$_->[2] } } @$table;

    Tx all! :)
      I think grep makes more sense in this case. Assuming you know there is only one result per $id, and don't mind checking the entire datastructure, this should work:
      my $domain_ip = (grep {$_->[0] == $domainid} @$table)[0]->[2];
      As in the folloing example:
      #!/usr/bin/perl -wT use strict; my $table = [ [7, 'tigers', 600], [8, 'elephants', 250], [9, 'lions', 500], ]; my $id = 8; my $fieldindex = 1; my $animal = (grep {$_->[0] == $id} @$table)[0]->[$fieldindex]; print "id=$id fieldindex=$fieldindex => animal=$animal\n"; =OUTPUT id=8 fieldindex=1 => animal=elephants


Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://122486]
Approved by root
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (3)
As of 2022-05-17 23:44 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (68 votes). Check out past polls.