in reply to retrieving the reply count of a thread using regular ol' DBI and CGI::Application

Your new syntax looks a little off. Here is how it should work: 

#get the reply count...
my $sth2 = $dbh->prepare("SELECT COUNT(*) FROM h_replies WHERE thread_
+id=?");
my $do = $sth2->execute() || die "Error counting posts: $DBI::errstr\n
+";

while(my $count = $sth2->fetchrow_arrayref) {
   push @rows_data, { count => @{$count}[0] };
}
$sth2->finish();
return wantarray ? @rows_data : \@rows_data;

[download]
It looks like you misunderstand how the count feature works in MySQL and how the db was going to return the data. Here I used arrayref but you could also use hashref if you so desired.

www.perlskripts.com
  • Comment on Re: retrieving the reply count of a thread using regular ol' DBI and CGI::Application
  • Download Code

Replies are listed 'Best First'.
Re^2: retrieving the reply count of a thread using regular ol' DBI and CGI::Application
by stonecolddevin (Parson) on Oct 11, 2005 at 04:37 UTC

    This doesn't account for the first SQL query from which I obtain the id's of the original threads however.

    What i need is to retrieve the messages, and from that take the id and pass it through the second SQL query from where i get the COUNT(*). This count up the total replies that have a thread_id that matches the id passed through the ->execute()

    meh.
[reply]
[d/l]
[select]

      This works for me: 

      use DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=count.db","","");

my $sth1 = $dbh->prepare('select id from threads');
my $sth2 = $dbh->prepare('select count(*) from replies where thid = ?'
+);

$sth1->execute;

for my $thid ( @{$sth1->fetchall_arrayref} )
{
    print "Thread: ", $thid->[0], "...\n";
    $sth2->execute($thid->[0]);
    print "Replies for thread ", $thid->[0], ": ", $sth2->fetchrow_arr
+ay, "\n";
}

$sth1->finish;
$sth2->finish;
$dbh->disconnect;

__END__

SQL Schema:
sqlite> create table threads ( id integer primary key, title varchar(2
+56) );
sqlite> create table replies ( id integer primary key, thid integer, t
+itle varchar(256) );
sqlite> insert into replies values (1, 1, 're: test1' );
sqlite> insert into replies values (2, 1, 're: re: test1' );
sqlite> insert into replies values (3, 2, 're: test2' );
sqlite> insert into threads values (1, 'test1');
sqlite> insert into threads values (2, 'test2');

Execution:
$ perl test_count.pl
Thread: 1...
Replies for thread 1: 2
Thread: 2...
Replies for thread 2: 1
$

      [download]

      Maybe your setup is different, but you should be able to translate appropriately...

      Best regards...

      --
      our $Perl6 is Fantastic;

[reply]
[d/l]
[select]

        This is the closest to what I was actually thinking of doing out of these threads...I'm sure faster than what I have posted as well

        meh.
[reply]

In Section Seekers of Perl Wisdom