How to count 2 tables one with articles and comments
Posted: Wed Nov 18, 2009 2:36 am
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