Categories > TinyButStrong general >

Record Count

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: Trent
Date: 2006-12-07
Time: 00:11

Record Count

I'm having a bit of trouble.  I'm working on small forum script and what I'm trying to achieve is the following:


First Topic Here                  X Posts
Second Topic Here              Y Posts

X and Y are the total posts for each topic, and that's the information I'm trying to retrieve.

Here is my code so far.

$XCM->MergeBlock('forum_cats',$cnx_id,'SELECT * FROM ' . FORUM_CATS . '') ;
$XCM->MergeBlock('forum_cat_count',$cnx_id,'SELECT COUNT (*) FROM ' . FORUM_TOPICS . ' WHERE (cat_id=\'%p1%\')');

And here is my HTML:
<tr valign="top">
    <td>  [forum_cats.topic;block=tr] </td>
    <td>   [forum_cats.$] </td></tr>
  <tr valign="top">

What I get when I run the script is

Topic 1                1 Post
Topic 2                2 Posts

Currently there is only 1 Post Total so Topic 2 should show O Posts.
I'm sure I've got something wrong in my "COUNT" statement.  Can somebody help me out?
By: sheepy
Date: 2006-12-07
Time: 05:13

Re: Record Count

You may wish to change the query to

'SELECT c.*, COUNT (t.id) AS count FROM ' . FORUM_CATS . ' c LEFT JOIN '. FORUM_TOPICS . ' t ON t.cat_id = c.id GROUP BY C.id'

Then you'll have a count field.  TBS's count field is 'block count', count of data merged, not count of some child of data.

It's possible to do it your way, in two merge, but you need to merge the name of second block during first merge, it's easier and faster to do it in one go.
By: Trent
Date: 2006-12-07
Time: 18:34

Re: Record Count

Okay, so that makes sense, but I'm really new at using JOIN statements and I'm not sure how to integrate the count into my template structure so it will display the topic count.
By: Trent
Date: 2006-12-10
Time: 18:54

Re: Record Count

Okay, finally figured it out.  I just had to make a few field name changes to the code you posted and did a little more research on JOIN statements and finally got it working.  Only took me 2 days.  LOL  Thanks so much for the help.
By: sheepy
Date: 2006-12-11
Time: 09:20

Re: Record Count

Welcome to the community of TBS.  Hope you'll enjoy it as much as we do. =)
By: RobN
Date: 2007-02-01
Time: 20:47

Re: Record Count

Hi,

could you be so kind to share your solution with us (the code)? I came across the same problem and if possible I would like to get to the result a bit faster than 2 days.

Thanks a lot,
Rob
By: Trent
Date: 2007-02-01
Time: 20:50

Re: Record Count

Well, i've long since abandoned that project and moved on to something else but I'll look for my source code and post it for you when i find it.