Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

dbi question

by philosophia (Sexton)
on Mar 04, 2005 at 17:55 UTC ( #436685=perlquestion: print w/replies, xml ) Need Help??

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

i'm using fetchrow_hashref to extract rows out of a database like
while(fetchrow_hashref) {print qq($data->{field1}\n);
-what is the best way, from within the while loop, to execute a query using the value of $data->{field1}?

i.e.,
while(fetchrow_hashref) { print qq($data->{field1}; # pseudocode to use $data->{field1} in another query, run query, anoth +er while(fetchrow_hashref) to print out the results of the second que +ry then... # end original while loop }
?
also, if anyone has dealt with this before, if the first while(fetchrow_hashref) returns identical values for $data->{field1} in subsequent iterations of the loop, know not to run the second query again on the same value for $data->{field1}?

thanks

janitored by ybiC: Minor format tweaks, including balanced <code> tags around snippets

Replies are listed 'Best First'.
Re: dbi question
by JediWizard (Deacon) on Mar 04, 2005 at 18:17 UTC

    my $q1 = $dbh->prepare("sql statement"); my $q2 = $dbh->prepare("sql statement 2 with ?"); $q1->execute(); while($data = $q1->fetchrow_hashref()){ $q2->execute($data->{field}); while($more_data = $q2->fetrow_hashref()){ # code here } }

    You can execute a prepared statement any number of times, each time passing in a number of values to "bind" to ? placeholders within the SQL. Some databases even support "named" placeholders ie:

    my $sth = $dbh->prepare("select * from table where field = :nam"); $sth->bind_param(':nam', $value); $sth->execute();

    See DBI and look for "Statement Handle Methods" for details.


    A truely compassionate attitude towards other does not change, even if they behave negatively or hurt you

    —His Holiness, The Dalai Lama

Re: dbi question
by FitTrend (Pilgrim) on Mar 04, 2005 at 20:02 UTC

    The only comment I would add on this is that what you will do may slow your program down. I mean if you execute a query inside a loop that has say 100,000 records, you'll be executing a query 100,000 time inside that loop.

    If the second query will be referencing the same index in a table, I would recommend creating a hash for the entire second dbi execute prior to executing the first query and performing the loop. This way, you're simply reference the hash for the value instead of performing all those queries. This may not work for what you need, but I thought it would be worth noting.

    example:

    ## ASSUMED CONNECTION ESTABLISHED ## # CREATE HASH FOR REFERENCE $sth = $dbh->prepare("select report_id, name from reportindex"); $sth->execute(); while ($ref = $sth->fetchrow_hashref()) { $reportIndex{"R$ref->{'report_id'}"} = $ref->{'name'}; } $sth->finish() unless (! $sth); $sth = $dbh->prepare("select reportindex_id from favorite_reports"); $sth->execute(); while ($ref = $sth->fetchrow_hashref()) { # REFERENCE HASH INSTEAD OF CALLING SECOND QUERY print "I like the report: ". $reportIndex{"R$ref->{'reportindex_id +'}"}; } $sth->finish() unless (! $sth);

    Hope this helps.

      Actually, and I'm no DBA, the OP looks more to me like a situation to use a JOIN of some sort. Your example may be a bit more simple than the general case, though.

      SELECT ri.name FROM reportindex AS ri, favorite_reports AS fr WHERE +ri.report_id = fr.reportindex_id
      Or something like that.

        This is true. I was being general. But you do bring up a good point that you should use SQL statements when possible to reduce the amount of perl code to manage.

      Depending on what percentage of the rows in the table in the second query that you expect to hit, you could cache the results of the inner query, something like this:
      my $inner_query = $dbh->prepare([inner query]); # $inner_query will have a bind param my $outer_query = $dbh->prepare([outer_query]); my %inner_cache; while ( my $data_ref = $outer_query->fetchrow_hashref() ) { if ( ! exists $inner_cache{$data_ref->{some_field}} ) { $inner_query->execute($data_ref->{some_field}}; $inner_cache{$data_ref->{some_field}} = $inner_query->fetchrow_h +ashref(); $inner_query->finish(); } my $inner_result = $inner_cache{$data_ref->{some_field}}; # do whatever } ...

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (3)
As of 2022-05-24 18:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (84 votes). Check out past polls.

    Notices?