SQL Query

[redfinite]

Could anyone please help me with an SQL query?

I would like to run an SQL query to get a list of how many threads each user has created in a specific forum ID.

Output would show:

User1: 24 threads
User2: 50 threads
User3: 23 threads

etc etc

Thank you.

[squidsk]

Originally Posted by redfinite

Could anyone please help me with an SQL query?

I would like to run an SQL query to get a list of how many threads each user has created in a specific forum ID.

Output would show:

User1: 24 threads
User2: 50 threads
User3: 23 threads

etc etc

Thank you.

The query is pretty straight forward:

SELECT username, count(*) AS threads 
FROM user 
LEFT JOIN thread ON (user.userid = thread.postuserid)
WHERE thread.forumid = 2
GROUP BY username

NOTE: In the SQL above I have excluded a table prefix which you'd need to add if your vbulletin install uses table prefixes.

If you were doing the query in php it would be as follows:

PHP Code:

$db->query_read("SELECT username, count(*) AS threads 
FROM " . THREAD_PREFIX . "user AS user
LEFT JOIN " . THREAD_PREFIX . "thread AS thread ON (user.userid =  thread.postuserid)
WHERE thread.forumid = 2
GROUP BY username"); 


You'd then loop through the results of the query and create the output in whatever format you want.