SQL Query Question

Mystis

Hey everyone, I've been trying to write a MySQL query but I've been running into some extreme problems. I want to return an associative array named $userposts, with the key 'forumid' => 'posts_in_forum'. I understand that this can be done with an SQL query(s), but I'm not sure how to write it or what the most efficient way of accomplishing this would be. I'm trying to use the data in a BBS to retrieve the top 5 forums in which the user has posted in. I'd appreciate any help! Thanks 🙂

Relative DB Schema

Table 'forums' with field forumid
Table 'threads' with fields 'threadid' and 'forumid'
Table 'posts' with fields 'postid' 'threadid' and 'userid'

nemonoman

for userid 9999

SELECT forums.forumid,
COUNT (posts.postid)
FROM forums, threads, posts
WHERE forums.forumid=threads.forumid
AND threads.threadid=posts.threadid
AND posts.userid=9999
GROUP BY posts.postid
LIMIT 0, 5

I think that'll work. It will at least get you started.

Mystis

Thanks, I've been working on it some and I've been able to get some progress. I've been able to figure out how many posts there were by a certain user in each thread...

SELECT count(post.postid) as postsinthread, post.threadid as threadid from post WHERE post.userid='1' group by threadid

And I've been able to add up the threads in a forum...

SELECT COUNT(thread.threadid) AS threadsinforum, thread.forumid AS forumid FROM thread GROUP BY forumid

But what I can't figure out is how you would take the results of the former query and add the number of posts in each thread up together where the thread shares the same forumid. Anybody think they could help me? Thanks!

Mystis

I've got it everyone, thanks for the help nemonoman.

[deleted]

"SELECT forums.forumid,
COUNT (posts.postid)
FROM forums, threads, posts
WHERE forums.forumid=threads.forumid
AND threads.threadid=posts.threadid
AND posts.userid=9999
GROUP BY posts.postid
LIMIT 0, 5

I think that'll work. It will at least get you started."

Remember that if you use GROUP BY, you cannot select anything other than aggregates or columns that are mentioned in the GROUP BY.

In this example you can select posts.postid, but you _can not_forums.forumid.

Mystis

Thanks for the help, I finally got it working 🙂