How to count 2 tables one with articles and comments
Moderator: General Moderators
-
huntsvillepr
- Forum Newbie
- Posts: 7
- Joined: Wed Nov 18, 2009 2:21 am
How to count 2 tables one with articles and comments
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
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
-
hemakumarrr
- Forum Newbie
- Posts: 7
- Joined: Mon Nov 09, 2009 12:42 am
Re: How to count 2 tables one with articles and comments
COUNT(*) is an aggregate function. It returns a single value. You have to write separate Query for COUNT.
-
huntsvillepr
- Forum Newbie
- Posts: 7
- Joined: Wed Nov 18, 2009 2:21 am
Re: How to count 2 tables one with articles and comments
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?
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?
- angelicodin
- Forum Commoner
- Posts: 81
- Joined: Fri Nov 13, 2009 3:17 am
- Location: Oregon, USA
Re: How to count 2 tables one with articles and comments
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.
- iankent
- Forum Contributor
- Posts: 333
- Joined: Mon Nov 16, 2009 4:23 pm
- Location: Wales, United Kingdom
Re: How to count 2 tables one with articles and comments
You need to do something like this:
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
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);Or at least I think thats right
hth
-
huntsvillepr
- Forum Newbie
- Posts: 7
- Joined: Wed Nov 18, 2009 2:21 am
Re: How to count 2 tables one with articles and comments
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?
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?
- iankent
- Forum Contributor
- Posts: 333
- Joined: Mon Nov 16, 2009 4:23 pm
- Location: Wales, United Kingdom
Re: How to count 2 tables one with articles and comments
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;-
huntsvillepr
- Forum Newbie
- Posts: 7
- Joined: Wed Nov 18, 2009 2:21 am
Re: How to count 2 tables one with articles and comments
This is fantastic! Thanks a million!