php - MySQL get count of all rows in another table where the value equals column in current table -

php - MySQL get count of all rows in another table where the value equals column in current table -

i'm programming forum in php, , i've come part i'll need count amount of threads in forum. i'm using next query forums , respective categories:

select f.id , f.name , f.description , c.id category_id , c.name category_name , c.description category_description forum_forums f bring together forum_forums_categories fc on f.id = fc.forum_id bring together forum_categories c on fc.category_id = c.id;

it gets job done , i'm able grouping categories. want next, add together amount of threads in forum each row in results, , i'm unsure how that.

i have next tables: forum_forums, forum_threads, forum_categories. also, threads can belong multiple forums (i have forums_threads_forums table binds specific thread_id forum_id).

so guess need add together count in original command somewhere. count need count rows in forum_threads_forums table forum_id equal of current forum adding results.

to create things simpler, here illustration of i'm trying accomplish (simplified): table: forum_forums

id name 1 forum1 2 forum2 3 forum3

table: forum_threads

id title 1 thread1 2 thread2

table: forum_threads_forums

thread_id forum_id 1 1 1 2 2 1 2 3

then query homecoming (amongst other things):

forum_forums.id forum_forums.name forum.threads 1 forum1 2 2 forum2 1 3 forum3 1

if force me in right direction great.

edit:

i think might need subquery such select count(thread_id) thread_count forum_threads_forums forum_id=:forum_id i'm unsure place in original query

answer:

for future reference, here working command i'm using now:

select forum_forums.id, forum_forums.name, forum_forums.description, count(forum_threads_forums.thread_id) thread_count, forum_categories.id category_id, forum_categories.name category_name, forum_categories.description category_description forum_forums left outer bring together forum_threads_forums on forum_forums.id=forum_threads_forums.forum_id inner bring together forum_forums_categories on forum_forums.id=forum_forums_categories.forum_id inner bring together forum_categories on forum_forums_categories.category_id=forum_categories.id grouping forum_forums.id

i uncertainty there's entry level tutorial on aggregate functions fails cover this, anyway...

drop table if exists forums; create table forums (forum_id int not null auto_increment primary key ,forum_name varchar(12) not null unique ); insert forums values (1 ,'forum1'),(2,'forum2'),(3,'forum3'); drop table if exists threads; create table threads (thread_id int not null auto_increment primary key ,title varchar(12) not null ); insert threads values (1 ,'thread1'), (2 ,'thread2'); drop table if exists threads_forums; create table threads_forums (thread_id int not null ,forum_id int not null ,primary key(thread_id,forum_id) ); insert threads_forums values (1 ,1), (1 ,2), (2 ,1), (2 ,3); select f.* , count(t.thread_id) threads forums f bring together threads_forums tf on tf.forum_id = f.forum_id bring together threads t on t.thread_id = tf.thread_id grouping forum_id; +----------+------------+---------+ | forum_id | forum_name | threads | +----------+------------+---------+ | 1 | forum1 | 2 | | 2 | forum2 | 1 | | 3 | forum3 | 1 | +----------+------------+---------+

note solution not show forums there no threads. that, you'd need utilize left [outer] bring together instead

php mysql pdo