Page 1 of 1

Select * from table then work individually with results

Posted: Thu Jun 17, 2010 2:54 pm
by tom8521
I have a news feed on my website where users can comment on these pieces of news.
It would be nice of the total number of comments left for each article were totaled and returned just beneath the news article.

My trouble is that all that is being totaled with the below code is the entire number of comments left for ALL news articles ever written.

Please can you make it only return the correct number of articles for that specific piece of news?
Thanks!

Code: Select all

$sql = "SELECT COUNT(*) FROM comments GROUP BY News";
$result2 = mysql_query($sql);
$num = mysql_result($result2, 0);

$result = mysql_query("SELECT * FROM news ORDER BY Date desc");

while($row = mysql_fetch_array($result))
  {
  echo "<div id=\"news-feed\"><div id=\"news-title\">" . $row['Title'] . "<br /></div>";
  echo "<div id=\"news-date\">" . $row['Date'] . "<br /><br /></div>";
  echo "<div id=\"news-story\">" . $row['Story'] . "<br /></div>";
  echo '	<div id="comment-links">' . $num . ' comments' . '</div>';
  
  if (isset($_SESSION['Id'])) {
	echo '	<a href="comment.php?news-id=' . $row['Id'] . '" id="comment-links">Leave a comment </a> ';
	}
  
  echo "</div>";
  }

Re: Select * from table then work individually with results

Posted: Sat Jun 19, 2010 12:24 pm
by andyhoneycutt
I'm assuming you have a column in the comments table that relates to the Id of the news article in the news table:

Code: Select all

$sql = "SELECT COUNT(*), NEWS_ID FROM comments GROUP BY NEWS_ID";