Page 1 of 1

How to count 2 tables one with articles and comments

Posted: Wed Nov 18, 2009 2:36 am
by huntsvillepr
Hi

I have two tables that i can trying to count to no avail.

table 1. is named - dir_link_comments and have columns - id, id_comment, title, etc (id_comment is the same variable as id in dir_categories
table 2 is named - dir_categories (article table) and have columns - id, path, title, etc

I am trying to list ALL articles with the number of comments beside each article. For those article with 0 comment, 0 comments should be echoed beside them

Here is some code that i developed, but it only prints articles that have comments and excludes those without.

$result = mysql_query("SELECT dir_categories.title, dir_categories.path, COUNT(*) AS total FROM dir_link_comments, dir_categories WHERE dir_link_comments.id_comment = dir_categories.id GROUP BY dir_categories.id" );
if (!$result) {
exit('<p> Error with comments');
}

while ($row = mysql_fetch_array($result))

{
$id_comment = $row['id_comment'];
$total = $row['total'];
$title = $row['title'];
$path = $row['path'];

echo '<a href="/' . $path .'/">' . $title . '</a> ' .$total . ' Comments <p>';

}

Please help

Re: How to count 2 tables one with articles and comments

Posted: Wed Nov 18, 2009 2:58 am
by hemakumarrr
COUNT(*) is an aggregate function. It returns a single value. You have to write separate Query for COUNT.

Re: How to count 2 tables one with articles and comments

Posted: Wed Nov 18, 2009 3:04 am
by huntsvillepr
I read where having separate queries is not good if you have a large database as this could slow down things.

What i have so far works, but it ONLY prints articles that have comments.

I wanted it to print articles without comments as well

How can i do that?

Re: How to count 2 tables one with articles and comments

Posted: Wed Nov 18, 2009 3:20 am
by angelicodin
I'm new to these advanced sql queries, but I have recently found that you could use something called JOIN or LEFT JOIN and do a num rows count in php. I don't know if that would help here.

Re: How to count 2 tables one with articles and comments

Posted: Wed Nov 18, 2009 3:50 am
by iankent
You need to do something like this:

Code: Select all

SELECT d.title, d.path, count(c.id) as comments FROM dir_categories d INNER JOIN dir_link_comments c ON (c.id_comment=d.id) GROUP BY (d.id);
That's basically going to get the Title, Path and Count(c.id) from the dir_categories table. Nothing is actually retrieved directly from the comments table, it just groups the items by d.id and counts them.

Or at least I think thats right :) sometimes my queries are trial and error lol, so try it and see if it works :)

hth

Re: How to count 2 tables one with articles and comments

Posted: Wed Nov 18, 2009 4:07 am
by huntsvillepr
Thank you. I got the same result as I had before:

It ONLY prints the articles with comments.

I want it to print ALL articles, those with and those without comments

with 0 comments echoed beside those without comments

Any other thoughts?

Re: How to count 2 tables one with articles and comments

Posted: Wed Nov 18, 2009 4:17 am
by iankent
Hmm, try this instead. Sorry, brain isn't quite with it yet this morning :)

Code: Select all

SELECT d.title, d.path, COUNT(c.id) as comments FROM dir_categories d LEFT JOIN dir_link_comments c ON (c.id_comment=d.id) GROUP BY d.id;

Re: How to count 2 tables one with articles and comments

Posted: Wed Nov 18, 2009 4:22 am
by huntsvillepr
This is fantastic! Thanks a million!