Member Post Count per Thread

[Zayexi Hayati]

I saw this in a VBulletin board that I think would be a nice mod to have on my YabbSE board. Especially since we tend to have races to see who can post the most in one particular thread.

What I am looking for is a post count for each member in a specific thread. I know we can count them up manually and that is what we have been doing lately but it would be nice to have it done automatically.

[Nemesis]

can you show me an examle of this?

[[Unknown]]

I think I know what he/you mean/s.  (although I would DESPISE these kinds of spam races on my board.)

Perhaps, somewhere under the avatar (next to posts...), the number of posts in THIS thread could be shown.

Personally, I would rather expand my "Posts per day" mod to cover Display.php (possibly InstantMessage.php) as well.... but, if you like spam wars this mod would be for you.

-[Unknown]

[Zayexi Hayati]

Here is an example of the board that I saw this at.
Click here
If you take a look at the number of replies this is a link to a total count of the posts ranked by members that have posted in each thread.

[Zayexi Hayati]

I think I know what he/you mean/s.  (although I would DESPISE these kinds of spam races on my board.)

Perhaps, somewhere under the avatar (next to posts...), the number of posts in THIS thread could be shown.

Personally, I would rather expand my "Posts per day" mod to cover Display.php (possibly InstantMessage.php) as well.... but, if you like spam wars this mod would be for you.

-[Unknown]

Well it is not something that we encourage on a constant basis. But we usually have one Thread where we do race to a certain number of posts. We do have some rules in place where one person can not just go crazy and post more than three times in a row. I will look into your mod though.

[Zayexi Hayati]

okay I have been thinking about this and I think I have come up with a solution. But I need some help.
Now the way I want this to work is that when you click on the number of posts a thread has it will popup a new window with a table of posters and the amount of posts that they have in this particular thread. Like so.

Member	#
User 1	5
User 2	4
User 3	3
User 4	3
User 5	1

I have looked at the yabbse_messages table and I do not see the need to add any extra fields or another table.
But now I am having problem getting the correct query for this purpose. I started with this one

Code Select Expand

SELECT `posterName` FROM `tsb_messages` WHERE `ID_TOPIC` = 1 GROUP BY `posterName` DESC
But that only gives me a one column table with the posters and no totals. Like this

posterName

zayexi

YaBB SE Dev Team

skuzzel

Rufus Bzb Jones

poruvi

Nikita

mrmeaner

Hug-In

Alia McCarros

Then I modify the query to add a SUM for the total column. Like this.

Code Select Expand

SELECT `posterName` FROM `tsb_messages` WHERE `ID_TOPIC` = 1, SUM(IF(ID_TOPIC='1',1)) AS total GROUP BY `posterName` DESC
But that gives me a syntax error. I tried COUNT and I tried it with and without commas(,) with and without quotes (',") both single and double.
If someone can help me with this query I think I can do the rest.
Thanks in advance.

[Zayexi Hayati]

Code Select Expand

SELECT `posterName` FROM `tsb_messages` WHERE `ID_TOPIC` = 1, COUNT(*) AS total GROUP BY `posterName` DESC

Tried COUNT(*) instead and still no luck.
Replaced (*) with posterName and still no luck.

[[Unknown]]

SELECT COUNT(ID_MSG)
FROM {$db_prefix}messages
WHERE ID_MEMBER=$posterID
  AND ID_TOPIC=$threadid;

[edit: d'oh... forgot the from.]

-[Unknown]

[Zayexi Hayati]

but wont that only give me the total posts for one member?
Would a seperate query have to be done for each member?

[Zayexi Hayati]

tweak your suggestion a bit and I almost got it working.

Code Select Expand

SELECT ID_MEMBER, COUNT(ID_MSG)
FROM tsb_messages
WHERE ID_TOPIC=6 GROUP by ID_MEMBER

[[Unknown]]

"SELECT COUNT(ID_MSG), ID_MEMBER
FROM {$db_prefix}messages
WHERE ID_MEMBER IN (" . implode(',', $ID_MEMBERsWhoPosted) . ")
  AND ID_TOPIC=$threadid
GROUP BY ID_MEMBER
LIMIT " . count($ID_MEMBERsWhoPosted)

That would be the fastest way... I think that should work. (untested!)

Of course, that would be much easier to do in my secret project, because it's already set up to be faster...

-[Unknown]

[Zayexi Hayati]

Okay you lost me  
I got this to work.

Code Select Expand

SELECT posterName, COUNT(ID_MSG)
FROM tsb_messages
WHERE ID_TOPIC=1 GROUP by posterName ORDER BY `COUNT(ID_MSG)` DESC

And the reason that I changed the order of the posterName and the COUNT is because that is how I want my table to appear.

[Zayexi Hayati]

And whats this secret project that you keep talking about... I am starting to get mighty curious  

Now, if I was to try out your solution I would like to know the answer to this questions:

Wheres does $ID_MEMBERsWhoPosted get defined?
This is a complete function correct?
If so, then that means that I should include it in my Subs.php file, or am I mistaken?
Last, Where do I call this function from MessageIndex.php?

Thanks for all the help.

[[Unknown]]

And whats this secret project that you keep talking about... I am starting to get mighty curious  

Now, if I was to try out your solution I would like to know the answer to this questions:

Wheres does $ID_MEMBERsWhoPosted get defined?
This is a complete function correct?
If so, then that means that I should include it in my Subs.php file, or am I mistaken?
Last, Where do I call this function from MessageIndex.php?

Thanks for all the help.

In my secret project, it collects and loads the member data in one swoop.  This can be MUCH FASTER when many people have replied to a single thread.

I named it that so you could tell what I meant by it... in mine, it's $posters (an array).

What are you talking about - functions?  My secret project is a rewrite of YaBB SE 1.5.1 with a load of extra features and speed ups.

-[Unknown]

[Zayexi Hayati]

Hmm okay... As for functions. Well I wanted to see if I could release this as a mod since I recieved two emails from other members that thought this would be a cool feature but I have no idea as to how to make a mod. Or what needs editing where. I am still learning.
But thanks for your help anyways. If nothing else I can always execute this query from phpMyAdmin whenever we need a total.

Thanks again.