Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Looping with DBI

by Samn (Monk)
on Jun 30, 2002 at 04:29 UTC ( #178308=perlquestion: print w/ replies, xml ) Need Help??
Samn has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to perform an SQL queary inside a while loop that uses previous quearied data with DBI. I am returning only the first result, I am pretty certain it's because I'm overwriting $sth->fetchrow_array().

I can pull the first data into an array, but surely DBI has a proper method of doing this. Could someone explain and possibly locate the documentation on this for me? Gracia.


$sth = $dbh->prepare ("SELECT id, body FROM news");
$sth->execute ();
while (($news_id, $news_body) = $sth->fetchrow_array()) {
  $sth = $dbh->prepare ("SELECT COUNT(id) FROM comments WHERE id = $news_id");
  ($comments) = $sth->fetchrow_array();
  print "$news_id, $news_body, $comments";
  }

Comment on Looping with DBI
Re: Looping with DBI
by dws (Chancellor) on Jun 30, 2002 at 04:39 UTC
    I am trying to perform an SQL queary inside a while loop that uses previous quearied data with DBI. I am returning only the first result, I am pretty certain it's because I'm overwriting $sth->fetchrow_array().

    Or, it might be because you're overwriting $sth when you prepare a new query inside the loop.

      With drastically little knowledge of Perl syntax, I had assumed that "$sth->fetchrow_array()" was set in stone. Revisiting it now I suppose your point is that I can say "$foo->fetchrow_array()" in my second queary, which makes me both thrilled and sheepish.

      Thanks for the quick response, my answer and so much more.
      -S

Re: Looping with DBI
by gav^ (Curate) on Jun 30, 2002 at 05:09 UTC
    Here's how I'd do it:
    my $sth_news = $dbh->prepare("SELECT id, body FROM news"); $sth_news->execute; $sth_news->bind_columns(\my $news_id, \my $news_body, \my $comments while ($sth_news->fetch) { my $sth_comments = $dbh->prepare("SELECT COUNT(id) FROM comments W +HERE id = ?"); $sth_comments->execute($news_id); $sth_comments->bind_columns(\my $comments); $sth_comments->fetch; print "$news_id, $news_body, $comments"; }
    You should look through the DBI docs for information on binding variables, it's bother faster and (in this case) more readable.

    gav^

      If you're going to go through the trouble of using bind variables, why not move the second prepare outside the while? That way you avoid needless re-prepareing of the same statement on every iteration of the loop...

      my $sth_news = $dbh->prepare(" SELECT id, body FROM news "); $sth_news->execute; $sth_news->bind_columns(\my $news_id,\my $news_body); my $sth_comments = $dbh->prepare(" SELECT COUNT(id) FROM comments WHERE id = ? "); while ($sth_news->fetch) { $sth_comments->execute($news_id); $sth_comments->bind_columns(\my $comments); $sth_comments->fetch; print "$news_id, $news_body, $comments"; }

          --k.


Re: Looping with DBI
by Ryszard (Priest) on Jun 30, 2002 at 09:57 UTC
    1stly I'd be using a bindvar for two reasons:
    1. Performance. You dont mention your db engine, however a decent RDBMS will hash your statement, then look the hash up in an internal table. If it finds a match it will used the previous statement. If not it will re-parse the statement. What you are doing by not using a bindvar (?) is making the statement change with each value, hence extra overhead for the engine to reparse essentially the same statement.
    2. Character parsing. If you use bindvars (also called placeholders) you dont have to worry about escaping characters such as single quotes! If you havent thought of this, consider the impact of:'value';drop comments; as the value of $news_id

    To the orig. question, I personally prefer to use fetchrow_arrayref() then push a copy of it onto a return array. It works out kind a like a 2D array of columns in the "inside" array and rows on the "outside" array. It makes it nice and easy to process.

    BTW, you have to make a copy of the container, as it is re-used for each row returned:

    while (my $retval = $sth->fetchrow_arrayref()){ push @retary, [$retval]; }
    should do it.. :-)

    HTH!

Re: Looping with DBI
by Aristotle (Chancellor) on Jun 30, 2002 at 10:54 UTC
    Just a hint: it looks as though you're working without warnings or strict. While their restrictions cause extra work, they will catch many typos and will prevent you from leaking variables across scopes you thought were independent - both of which can cause very subtle bugs that can be almost impossible to track down. Do yourself a favour, get in the habit of using my generously and make it second nature to start every script with
    #!/usr/bin/perl -w use strict;

    (Or, if you're using a reasonably recent version of Perl, use warnings; rather than the -w.)

    In your case f.ex, the following code:
    my $sth = $dbh->prepare ("SELECT id, body FROM news"); $sth->execute (); while (my ($news_id, $news_body) = $sth->fetchrow_array()) { my $sth = $dbh->prepare ("SELECT COUNT(id) FROM comments WHERE id += $news_id"); $sth->execute (); # was this missing from your snippet accidentall +y? my ($comments) = $sth->fetchrow_array(); print "$news_id, $news_body, $comments"; }
    while rather not recommendable as a casual reader might confuse the inner and outer $sth with one another, would at least have worked.

    Makeshifts last the longest.

Re: Looping with DBI
by rdfield (Priest) on Jun 30, 2002 at 17:45 UTC
    Looks like you need an SQL join:
    select a.id,a.body,b.count(*) from news a, comments b where a.id = b.id(+) group by a.id,a.body
    it'll work faster, and there's no bind params or overwriting the statement handle to worry about.

    note: the (+) is the outer join operator in Oracle, change it to whatever's required in the RDBMS you use

    rdfield

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (11)
As of 2014-09-19 08:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (133 votes), past polls