News comments count

Question

I'm tired of searching for the solution my news comment system. What i have. I have 2 different mysql tables item (id, title, category_id, details) and comments (id, item_id, comment) If i have single news then counting is fine like here in picture: Single news

Code:

if (!empty($comments)) {
$comm = count($comments);
}  else {
$comm = 0;
}

But if i use same code category view, result is: Category view

If i use code:

$sqls = mysql_query("SELECT c.item_id,
       COUNT(c.comment) 
FROM   comments c 
       RIGHT JOIN items i 
         ON c.item_id = i.id 
GROUP  BY c.item_id");
$comm = mysql_num_rows($sqls);

$smarty->assign('comm',$comm);

Result is :Some number of comments

How to make possible to see the Category View the correct number of comments?

If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Nov 16 '15 at 14:25
Use `group by categor_id, item_id` and `select gategory_id,item_id,count(item_id) — Laurentiu, Nov 16 '15 at 14:30
Output result: News item1 - (1) News item2 - (14) News item1 - (12) --- (inside comments number) — rainb, Nov 16 '15 at 15:16
No, I do not. I do not understand anything anymore :). A little bit too difficult for me. However, it should be to get to work. — rainb, Nov 19 '15 at 17:15

Mark Ng · Answer 1 · 2015-11-20T14:10:24.430

TRY this example. The result is the same as the screenshot below but with some background colour to distinguish the difference. Of course you should change to your own connection and layout.

<?php
$db = new PDO('sqlite:news.db');//change to your own
$sql = $db->prepare("
    SELECT item.id, item.title, item.details, comments.comment, COUNT(comments.id) AS NumberOfComment
    FROM item LEFT JOIN comments ON item.id = comments.item_id
    GROUP BY item.id ORDER BY item.id");            
$sql->execute();
$row = $sql->fetchAll();
//get comment
$comment = $db->prepare("SELECT comment FROM comments WHERE item_id = ?");
foreach ($row as $rows){
    echo "<br>";
    echo "Title: ".$rows['title'];
    echo "<br>";
    echo "Details: ".$rows['details'];
    echo "<br>";
    echo "<i>".$rows['NumberOfComment']." comments </i>";
    echo "<br>";
    $comment->execute(array($rows['id']));
    $comments = $comment->fetchAll();
    echo '<div style="background-color:pink;">';
    foreach ($comments as $showcomment){
    echo $showcomment['comment'];
    echo "<br>";
    }
    echo "</div>";
    echo "<br>";    
}
?>

example item table

example comment table

and the result is...

News comments count

1 Answers1