selecting data from mysql

[msz900]

I have an issue in select query. I want to select total_comment From comment table and show it under each post.

What I Try

SELECT SUM(total_comment) AS comment
FROM   user_comment
       INNER JOIN post
               ON user_comment.image_id = post.id
WHERE  status = 0

And

SELECT SUM(total_comment) AS comment 
FROM   user_comment 
WHERE  status = 0 

Problem:

I am not able to get the total comment posted on each post... The result i get is wrong.

Result Pic..

Link

Or

Link

But I Want Like...

Link

This is post table image..

Link

This is user_comment table image

Link

My php code is

<?php
session_start();
include 'conn.php';
if(!isset($_SESSION['user']))
{
    header('location:signin.php');
}



$smt=$conn->prepare("SELECT * FROM post,images WHERE post.Id=images.Id");
$smt->execute();
$smtt=$conn->prepare("SELECT * FROM post,images WHERE post.Id=images.Id");
$smtt->execute();
$myres=$smtt->fetch(PDO::FETCH_OBJ);
$imgid=$myres->Id;
$qry=$conn->prepare("SELECT COUNT(user_comment.Total_Comment) AS comment FROM user_comment INNER JOIN post ON user_comment.Image_Name = post.File_Name WHERE user_comment.Image_Id =post.Id");
$qry->execute();
$result=$qry->fetch(PDO::FETCH_OBJ );
$total_coment=$result->comment;
?>
<?php include 'header.php';?>
<?php
if(isset($_SESSION['user']))
{
    include 'nav.php';
}
else
{
    include 'nav-simple.php';
}
?>

    <div class="container-fluid">
    <?php include 'right_sidebar.php';?>
<div class="main-container-top" id="masonry-grid">
    <?php while($rows=$smt->fetch(PDO::FETCH_OBJ)): ?>

        <div class="col-md-3 grid-item post-col">
<img src="image/<?php echo $rows->Image_Name;?>" data-echo="image/<?php echo $rows->Image_Name;?>" class="post-image"/>

            <h5>Post On &nbsp;<?php echo $rows->Post_Date;?> <span class="pull-right">By <?php echo $rows->Post_By; ?></span> <span class="pull-right">Total Comment <?php echo $total_coment;?></span></h5>

            <a href="post-description.php?id=<?php echo $rows->Id ?>"> <h4><?php echo $rows->Post_Title;?></h4> </a>
            <p>
                <?php echo $rows->Post;?>
            </p>

        </div>


    <?php endwhile;?>
</div>
    </div>

<?php include 'footer-content.php';?>
<?php include 'footer.php';?>

[joshmac]

I think what you need is a group by clause after your WHERE clause:

GROUP BY comment.ID

[Santanu.Das]

Please try it

Select A.Id, B.comment From Post A, (Select Image_Id, COUNT(Total_Comment) As comment From user_comment Group By Image_Id) B Where B.Image_Id = A.Id