Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Re: Looping with DBI

by gav^ (Curate)
on Jun 30, 2002 at 05:09 UTC ( #178316=note: print w/ replies, xml ) Need Help??


in reply to Looping with DBI

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^


Comment on Re: Looping with DBI
Download Code
Replies are listed 'Best First'.
Re: Re: Looping with DBI
by Kanji (Parson) on Jun 30, 2002 at 11:27 UTC

    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.


Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (8)
As of 2015-07-30 17:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (273 votes), past polls