-1

I have a news and comment system where I want to see the number of comments for a particular news item. I tried many ways but most of them show me the number of all comments in the table but not the number of specific news. How should this be done?

I have table comments with id , nid(news id) comment and author.i tried this method but it shows me the number of all comments from the whole table.

$query = "SELECT * FROM comments ORDER BY nid";
$stmt = $db->prepare($query);
$stmt->execute();
$numberOfComments = $stmt->rowCount();
echo "(<a href=\"comments.php?id=".$id."\">Add Comment</a>) <hr> Comments ( ".$numberOfComments." )";

There is my SQL file comments.sql

CREATE TABLE `comments` (
`id` int(11) NOT NULL,
`nid` int(11) NOT NULL,
`c_title` varchar(70) NOT NULL,
`c_author` varchar(50) NOT NULL,
`comment` text NOT NULL,
`dateAdded` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `comments` (`id`, `nid`, `c_title`, `c_author`,  `comment`,`dateAdded`) 
VALUES(1, 1, 'My 1st comment', 'User', '1st comment text', '14/08/2019 ,   10:55:32'), 
(2, 1, 'My 2nd comment', 'Admin', '2nd comment here', '14/08/2019 ,  10:56:58'),
(3, 2, 'Comment test here', 'User', 'Comment test here', '14/08/2019 ,10:57:10');

In this case, I have a product to which I add a comment. I want to know how many comments each product has but I can't handle it. "nid" is my product ID.

Румен Радев
  • 11
  • 2

1 Answers1

1

Instead of count the rows return you could use proper sql function

you can use count(*) and group by

for single news_id 

 SELECT count(*) my_count
 FROM comments 
 WHERE nid = 1

for all new_id you coudl use 

 SELECT nid, count(*) my_count
 FROM comments 
 GROUP BY nid
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Doesn't work. By the first way for any news item shows me there is 1 comment and the second way shows me that there are 2 comments. Actually in the first news I have 2 comments in the table and in the second 1 – Румен Радев Aug 14 '19 at 07:50
  • if think you should check better for the result .. the first query retun just one row and you must inspect the my_count column alias for read the number of the row .. the second query return two rows with two column the first column is the nid and the second the number of comments rows for this nid .. answer updated for column name alias – ScaisEdge Aug 14 '19 at 07:53
  • Currently it shows me that there is 1 comment and 2 news. What exactly does 'my_count' do. I can't figure out how to show how many comments there are on the news, I don't understand much. – Румен Радев Aug 14 '19 at 08:02
  • update your question .. add a valid data sample as text .. so we can't try to explain how count(*) work .. – ScaisEdge Aug 14 '19 at 08:03
  • It's done. Can someone help ? – Румен Радев Aug 16 '19 at 15:36