Query to count user's Threads and Posts

[Dismounted]

If you are planning to do that in postbits, it will be fairly server intensive.

Originally Posted by romputer

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]

Originally Posted by Dismounted

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]

[sql]SELECT t.forumid
FROM post AS p
LEFT JOIN thread AS t USING (threadid)
WHERE p.postuserid = X[/sql]

PHP Code:

while ($post = $vbulletin->db->fetch_array($result))
{
    ++$posts["$post[forumid]"];
} 


Did you actually look in the post/thread tables to see the relations?

[romputer]

Originally Posted by Dismounted

[sql]SELECT t.forumid
FROM post AS p
LEFT JOIN thread AS t USING (threadid)
WHERE p.postuserid = X[/sql]

PHP Code:

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]

Originally Posted by romputer

What is postuserid?

The creator of the post... postuserid

[romputer]

Originally Posted by Dismounted

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'



--------------- Added [DATE]1235016809[/DATE] at [TIME]1235016809[/TIME] ---------------

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:
[sql]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[/sql]

[romputer]

Gracias!