I need MySql help. I'm confused.

Someone posted a question in mysql forum which almost resembles this one!
http://www.daniweb.com/forums/thread179456.html Modify those queries and it should work for you!

Hello! First of all, I hope I can help, let me explain how I would do this, I think this is your setup, but I can't be sure.

If you are doing a forum, then whenever someone ads a post, you should INSERT a new row into the posts row. This row would contain the ID of the actual post (ID), the ID of the user who left it (user_ID), the topic it is in (topic_ID) and finally the area of the forum it was left in (forum_ID).

This way, all the posts will be in one table, all you need to do is count how many there are with a specific user's ID. Here's an example with the specific ID "5".

SELECT COUNT(posts.ID) FROM posts WHERE user_ID = 5

You can also count the number of posts a user has left in a specific topic of the form by doing the following query:

SELECT COUNT(posts.ID) FROM posts WHERE user_ID = 5 AND topid_ID = 3

Hope I could help, let me know if you need some more info!

That's what I would've done too, but I didn't build the forums, I'm just trying to work with them. (IPB 2.3.5, if that helps.)

So, I can't alter the database schema. Basically, I am wanting to count the number of posts in a forum by a user, but the only way I can getting it is by using the forum id from the topics table and the username from the posts table.

Gah!

Hi Andrieux!

Ah I see. In this case you should be able to use JOIN to count it, something like this:

SELECT 
    count(posts.ID)
FROM 
     posts
JOIN
     topics
     ON
     posts.topic_id = topics.id    
JOIN
     forums
     ON
     forums.topic_id = topics.id 
WHERE
     posts.user_id = 5

Take a look, see if it works :)

SELECT count( ibf_posts.pid )
FROM ibf_posts
JOIN ibf_topics ON ibf_posts.topic_id = ibf_topics.tid
JOIN ibf_forums ON ibf_topics.forum_id = ibf_forums.id
WHERE ibf_posts.author_id = '1'

That returned the number of total posts for the user. If I had the forum id, how could I narrow the return with that?

I tried:

SELECT count( ibf_posts.pid )
FROM ibf_posts
JOIN ibf_topics ON ibf_posts.topic_id = ibf_topics.tid
JOIN ibf_forums ON ibf_topics.forum_id = ibf_forums.id
WHERE ibf_posts.author_id = '1'
AND ibf_forums.id = '1'