Member Avatar for Andrieux

I have three tables: posts, topics, and forums. Each row in post table has a topic id and a author name. Each row in topics has an id that corresponds with the topic_id in posts, and then it has an id for the forum that it (the topic, containing the post) is in. Each row in the forums table has an id that corresponds with the id in topics, and then a name of the forum. I want to count the number of posts a user has in a certain forum.

How would I do that?

  • 3 Contributors
  • 5 Replies
  • 167 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Andrieux
Member Avatar for danielpataki

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!

Member Avatar for Andrieux

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!

Member Avatar for danielpataki

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 :)

Member Avatar for Andrieux 
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'
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.