Count Comments?

[dottomm]

Hi, Thanks in advance for any help someone could offer.

I'm trying to display a list of stories submitted to my site from a table titled stories, the name of the person who submitted the story and how many comments about the story have been posted.

I'm working with three tables.

The Stories table [story_id] [user_id] [storytitle] [storycategory]
The Team Table [team_id] [teamname]
The Comments Table [comment_id] [story_id] [user_id]

my query should be something like:
Get the stories
(select * from stories)
Also the team name of the person who submitted them
	(inner join teams ON stories.user_id = teams.team_id) 
And count for me how many times a comment has been made for each story.
	(count how many comments.story_id =stories.story_id)

i can easily obtain the story titles and the team names that submitted with

SELECT * from stories 
	inner join teams ON stories.user_id = teams.team_id

but I have no idea how to count how many comments.story_id = stories.story_id

again,
thanks in advace.

[Shanti C]

$r=mysql_query("select count(*) as cnt form stories inner join teams ON stories.user_id = teams.team_id");
$t=mysql_fetch_array($r);
echo $t['cnt'];//prints count of stories..

[dottomm]

Thank you Shanti for your help, unfortunately that isn't quite what I am trying to do. Instead of counting how many stories are posted, or how many comments are posted, I want to know how many comments are tied to each individual story.

How many comments.story_id = stories.story_id

so my result would be:
story_id | story title | # of comments (from comment table)

So the query needs to be something like:
echo all the story titles and story_ids and the number of comments each record has received.

Again, thanks for any help or clues.

[Shanti C]

try this:

$r=mysql_query("select count(*) as cnt form commenttable comments.story_id = stories.story_id
");
$t=mysql_fetch_array($r);
echo $t['cnt'];//prints count of stories..

post your table columns names clearly....

[dottomm]

Again, thank you, but no luck.

I'm not sure what is wrong.

As an example, it would be like how the Topic/Threads listed on this site.

Thread Name | Replies | Views

I'm sure that info all lives in separate tables.
Can anybody take a stab at guess what the select statement would look like be on this page:
http://www.daniweb.com/forums/forum17.html

Thanks in advance,

-dottomm

[mcd]

try this (name the fields you want to select instead of using the star give you more versatility - like a sub-query):

SELECT s.story_id, s.user_id, s.storytitle, t.team_name
  (SELECT COUNT(c.story_id) FROM comments c 
    WHERE c.story_id = s.story_id) AS comment_cnt 
FROM stories s
INNER JOIN teams t ON s.user_id = t.team_id

comment_cnt should then be the number you're looking for. Note that this will not pull any of the comments themselves, just the count in each story.

[dottomm]

Yes! That is exactly what I am trying to do. Thank you for understanding.

However, I am stumped by how to display the 'comment_cnt'. I've not used the 'AS' statement before. How do I pass that as a variable?

thanks for getting me this far.

[dottomm]

Sorry to dig this old thread up again, but I am once again faced with a similar query problem.

I am try to to count how many posts a user has made to a forum

i have two tables:

t1 Posts
--------
id
username
------------

t2 users
------
user_id
username
title

Here is the query I an using and I can't figure out why It's not working. I keep getting an error on the line where the sub-query starts.

(Select users.user_id,users.username,posts.id  
  (select COUNT(posts.id) AS post_count from posts where users.user_id=posts.user_id)

in English; I want to display the users "username" and how many times the users "user_id" appears in the user_id column on the Posts table as 'post_count'.

Thanks in advance for any help anyone can offer.

[dickersonka]

select 
  count(*), 
   p.post_id, 
   p.user_id, 
   u.username 
from 
   Posts p 
inner join 
   Users u 
on 
   p.user_id = u.user_id
where
  p.user_id = ?

that will be for a single user, if you want something for a view, remove the where and query the view

[dottomm]

Yes! That is it. Thank you. I'm learning a lot here. Thank you everyone.