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

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

Hey all,

I'm using CGI::Application and DBI with MySQL to create a message board of sorts.

CGI::Application has surpassed my expectations as far as organizing my code and cutting down development time, and with that same thought in mind, I decided to toss out Class::DBI for speed. Once again this has worked.

Now the problem.

I'm attempting to retrieve a reply count on each individual thread to show in the main page.

Originally, I used

 for (@info) {
  my %data;
  my $sth = $obj->DBI->Replies->sql_count;
  $sth->execute($_->id);
  my $r = ($sth->fetchrow_array)[0];
  $data{author} = $_->author;
  $data{content} = $_->content;
  $data{title} = $_->title;
  $data{id} = $_->id;
  $data{date} = $_->date;
  $data{count} = $r;
  push @loop_data, \%data;
  }
[download]

where @info was the array of data retrieved by Class::DBI, and $obj->DBI->Replies->sql_count was defined in PL::DBI::Replies as

SELECT COUNT(*) from __TABLE__ where 
  thread_id=?
[download]

and of course, the original thread id was passed to the placeholder.

What I am trying now, and it's obviously not working, is this:

 #get the reply count...
  my $sth2 = $dbh->prepare(q{ select count(*) from dh_replies where th
+read_id=?});
  for ( scalar $rows_data ) {
  $sth2->execute($_);
  my $count = $sth2->fetchrow_array;
  push @$rows_data, { count => $count };
  }
[download]

I can't really tell you how I think this is going wrong, thus the reason I brought it before You, the illustrious monks.

UPDATE: Ok I lied. I think where I'm going wrong has to do with scalar $rows_data, and then $sth->execute($_)

Also, push @$rows_data, { count => $count } doesn't seem to be working either...

UPDATE^2: This works, but I'm sure there's a more efficient way to do it:

### get replies
    ### use each $row's id as the argument passed to execute and retri
+eve
    ### reply counts on each message
    my $sth2 = $dbh->prepare(q{ select count(*) from dh_replies where 
+thread_id=? });
    $sth2->execute( $rows->{'id'} );
    my $count = ($sth2->fetchrow_array)[0];
    
    my %data;
    $data{'subject'} = $rows->{'subject'};
    $data{'author'}  = $rows->{'author'};

    $data{'content'} = $rows->{'content'};
    $data{'date'}    = $rows->{'date'};
    $data{'id'}      = $rows->{'id'};
    $data{'count'}   = $count;
    push @rows_data, \%data;
  }
[download]

Please help me if you can, if any clarifications are needed let me know.

-devin

Comment on retrieving the reply count of a thread using regular ol' DBI and CGI::Application Select or Download Code

Replies are listed 'Best First'.
Re: retrieving the reply count of a thread using regular ol' DBI and CGI::Application by Elijah (Hermit) on Oct 11, 2005 at 02:25 UTC
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	[reply] [d/l]
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]
Re^3: retrieving the reply count of a thread using regular ol' DBI and CGI::Application by Excalibor (Pilgrim) on Oct 11, 2005 at 13:55 UTC
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]
Re^4: retrieving the reply count of a thread using regular ol' DBI and CGI::Application by stonecolddevin (Parson) on Oct 11, 2005 at 21:25 UTC
Re: retrieving the reply count of a thread using regular ol' DBI and CGI::Application by techcode (Hermit) on Oct 11, 2005 at 11:38 UTC
So $rows_data is an arrayref? Shouldn't it be something like : `foreach( @{$rows_data} ){ $sth2->execute($_); my $count = $sth2->fetchrow_array; push @$rows_data, { count => $count }; }` [download] Anyway, I'm still not clear of what this whole thing is doing. Could you post whole code snippet related to $rows_data ... Have you tried freelancing? Check out Scriptlance - I work there.	[reply] [d/l]
Re^2: retrieving the reply count of a thread using regular ol' DBI and CGI::Application by stonecolddevin (Parson) on Oct 11, 2005 at 21:26 UTC
Correct me if I'm wrong...but aren't `@{$rows_data}` && `@$rows_data` the same? meh.	[reply] [d/l] [select]
Re: retrieving the reply count of a thread using regular ol' DBI and CGI::Application by rhesa (Vicar) on Oct 11, 2005 at 13:14 UTC
You wrote: `for ( scalar $rows_data ) { $sth2->execute($_); my $count = $sth2->fetchrow_array; push @$rows_data, { count => $count }` [download] 1. Where does $rows_data come from? It looks like an arrayref, so you should treat it as such. Are you trying to get the number of elements in it, or are you iterating over it? If the former, then do `for( scalar @$rows_data )`; if the latter, do `for( @$rows_data )` 2. Pushing new values onto an array you're iterating over is likely to confuse the for() loop, and won't do what you want. Try using a new array for your hashrefs. That being said, are you sure one of Class::DBI's count() methods or one of the Count plugins won't be able to do this for you more effeciently? It's not entirely clear what you are trying to put together. Could you give us a better description of your data model, and your desired output model?	[reply] [d/l] [select]