sql count(threads), count(posts)

[pagegen]

Hi guys

 

I have a mysql db, am using php to create a forum

 

on the page which displays all the forums

 

I would like to show number of threads in that forum and posts

 

I am trying to do this but it wont work

 

"SELECT forums.*, COUNT(threads.id) AS total_threads, COUNT(posts.id) AS total_posts FROM forums INNER JOIN threads ON threads.forums_id=forums.id INNER JOIN posts on threads.id=posts.threads_id WHERE forums.deleted='0' AND threads.deleted='0' AND posts.deleted='0'"

 

I have 3 tables

 

forums | threads (has a forum id) | posts (has thread id)

 

as we never delete any rows, all tables have a field deleted which changes to 1 when a row is deleted

[requinix]

Use subqueries. Example:

SELECT forums.*,
(SELECT COUNT(1) FROM threads WHERE threads.forums_id = forums.id) AS total_threads,
...

[pagegen]

How thanks for your reply

but how will this work on posts as posts dont have the forum ID

[fenway]

Show us some sample output -- I don't know what "not works" means.

[requinix]

Assuming they have a thread ID instead, you do the same thing except the subquery now has a JOIN.

[pagegen]

this is what I have coded and it seems to work, but all advice on improvments are welcome

 

SELECT 
					threads.*, 
					(SELECT COUNT(posts.id) FROM posts WHERE posts.threads_id=threads.id AND posts.deleted='0') AS total_posts,
					username AS creator_name, members.id AS creator_id, 

					(SELECT posts.members_id FROM posts WHERE posts.threads_id=threads.id ORDER BY id DESC LIMIT 0, 1) AS lastposter_id,
					(SELECT username FROM posts INNER JOIN members ON posts.members_id=members.id WHERE posts.threads_id=threads.id ORDER BY posts.id DESC LIMIT 0, 1) AS lastposter_name
					FROM threads INNER JOIN members ON threads.members_id=members.id WHERE forums_id='".$g_forum."' AND threads.deleted='0'

 

here is what it looks like

link to thread | total replys | total pots