Query to count user's Threads and Posts

[discussionarena]

What is the query to discover the exact number of threads of a member and the number of posts ?

Thanks

.

[Xenon]

SELECT count(*) FROM thread WHERE postuserid = xx
SELECT count(*) FROM post WHERE userid = xx

note: that will also show soft deleted and under moderation posts/threads

[discussionarena]

Thanks

[Xenon]

you're welcome :)

[WoGuziczek]

thanks ;)

[romputer]

Following the logic, what would be the query to discover the exact number of posts for each forum the group "Registered Users" can see, per member?

Output something like this:
Member Name, Each Forum Name and Post Number
Frank, Forum1: 261, Forum2: 197, Forum3: 367

Thanks!

[Dismounted]

Not sure what you want there. Do you want the number of posts user X has done in each forum?

[romputer]

Not sure what you want there. Do you want the number of posts user X has done in each forum?Yes. Exactly. For every member of the usergroup "Registered Members" who has at least made one post in any forum.

Frank, Forum1: 261, Forum2: 197, Forum3: 367
Hank, Forum1: 11, Forum2: 39, Forum3: 912
Tank, Forum1: 732, Forum2: 1, Forum3: 214
Spanky, Forum1: 635, Forum2: 90, Forum3: 453

[Dismounted]

There is no "easy" query for that. You would have to select every post, and join the thread table to get the forum ID. Then loop through the results and increment the count of each user (this would be server intensive). However, doing this for just one user is much easier on the server, just limit the select to one user only.

[romputer]

okay, thanks.

I know I need a query but can't figure out how to write it. Anyone have an idea?

I want to have a more detailed post number listed for each member instead of just "posts". I have ten boards on the forum and would like to show how many posts each member has in each board.

[Dismounted]

If you are planning to do that in postbits, it will be fairly server intensive.
I know I need a query but can't figure out how to write it. Anyone have an idea?
Do you know how to use joins in MySQL?

[romputer]

If you are planning to do that in postbits, it will be fairly server intensive.
Do you know how to use joins in MySQL?I was hoping someone who is well versed in the db could just type out the query here. Yes, I know joins and such but I don't know the db that well.

Maybe have the output in the profile. Gives another member an idea where the person posts the most.

[Dismounted]

SELECT t.forumid
FROM post AS p
LEFT JOIN thread AS t USING (threadid)
WHERE p.postuserid = X
while ($post = $vbulletin->db->fetch_array($result))
{
++$posts["$post[forumid]"];
}
Did you actually look in the post/thread tables to see the relations?

[romputer]

SELECT t.forumid
FROM post AS p
LEFT JOIN thread AS t USING (threadid)
WHERE p.postuserid = X
while ($post = $vbulletin->db->fetch_array($result))
{
++$posts["$post[forumid]"];
}
Did you actually look in the post/thread tables to see the relations?Not as much as I could have. What is postuserid? Thanks.

[Dismounted]

What is postuserid?
The creator of the post... postuserid

[romputer]

The creator of the post... postuserid

SELECT t.forumid
FROM post AS p
LEFT JOIN thread AS t
USING ( threadid )
WHERE p.postuserid =803

MySQL said:

#1054 - Unknown column 'p.postuserid' in 'where clause'

:confused:

--------------- Added 1235016809 at 1235016809 ---------------

Okay, I am really lost. How about if I have one forumid and one userid, how can I get the number of posts that user made in that forum? Thanks in advance.

[Dismounted]

I think I made a mistake, postuserid is for threads. Here is the "proper" query:
SELECT COUNT(p.postid) AS count
FROM post AS p
LEFT JOIN thread AS t USING (threadid)
WHERE p.userid = 803
AND t.forumid = X

[romputer]

Gracias!