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

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

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.