http://www.perlmonks.org?node_id=178308

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

Replies are listed 'Best First'.
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.

[reply]
[d/l]
    178311
    by Samn (Monk) on Jun 30, 2002 at 04:44 UTC
    		[reply]
    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";
}

      [download]
      You should look through the DBI docs for information on binding variables, it's bother faster and (in this case) more readable.

      gav^

    		[reply]
    [d/l]

        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";
}

        [download]

            --k.


    		[reply]
    [d/l]
    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;

      [download]

      (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";
}

      [download]
      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.

    		[reply]
    [d/l]
    [select]
    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];
}

      [download]
      should do it.. :-)

      HTH!

    		[reply]
    [d/l]
    [select]
    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

      [download]
      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

    		[reply]
    [d/l]

    Back to Seekers of Perl Wisdom