joining a count on a query

0
votes

I have 2 tables: comments & blog

blog has the following fields: id(Unique key),title, author, body, date, img, imgdes, tags

comments : key(Unique key), postid(related to the id of blog),name, email, date, message

Im trying to display all of my blog post and the number of comments on every post.

So im trying to "count(postid) where postid=id"

I got something to work but its based around having 1 comment which wont work but this is it:

 SELECT a.postid,c.author,c.title, c.id,c.body,c.date,c.pic, c.tags, c.imgdesc,  
 COUNT(*) AS num_comments FROM comments a LEFT JOIN blog c ON c.id = a.postid 
 GROUP BY c.id order by id DESC"

Again this only work when everything has a comment and i get why but i cant figure out how to implement what I want.

To put it all out there i have:

 $sql="***( help 1 of 2) what to set the query to****"
 $query = mysql_query($sql) or die(mysql_error());
  <?php do{ ?>
  <html stuff here>
  <?php echo $blog['title']?><br>
  <?php echo $blog['*******(help 2 of 2) # of comments display here******']
  <?php } while($blog = mysql_fetch_assoc($sql));?>

im sure this is a easy join but i have no clue thanks!

4
phpmysqlsql

4 Answers

1
votes

Use this query it may works for you

SELECT a.postid,c.author,c.title, c.id,c.body,c.date,c.pic, c.tags, c.imgdesc, COUNT(a.key) AS num_comments FROM blog c left outer join comments a ON a.postid = c.id GROUP BY c.id order by id DESC
1
votes

if you want all the blog posts then it should be on the left side of the left join.

0
votes

I think the issue lies where you write: FROM comments a LEFT JOIN blog c ON c.id = a.postid GROUP BY c.id order by id DESC"

The comments a and blog c don't seem to be correct references.

Should it be FROM comments.a LEFT JOIN blog.c ON ...?

0
votes

Are you trying to count the number of comments in each blog? if yes then, SELECT c.postid,count(key) as num_of_comments FROM blog b, comments c WHERE b.id = c.postid GROUP BY c.postid

Another easy way out: As you have the postid as a foreign key, you can just get the results from comments table SELECT postid,count(key) as num_of_comments FROM comments GROUP BY postid