selecting data from mysql

See more:

SQL

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..

http://i.imgur.com/o40sw3T.png[^]

Or

http://i.imgur.com/16Ln2ds.png[^]

But I Want Like...

http://i.imgur.com/6bxH3eB.png[^]

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  <?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';?>

Note:

When i run the query In the post description the it would work fine, but in index page it will not...

My post-description.php code is

<?php
session_start();
include 'conn.php';
$pic_id='';
if(isset($_GET['id']))
{
    $pic_id=$_GET['id'];
}
$comv=$conn->prepare("SELECT * FROM user_comment WHERE user_comment.Image_Id='".$pic_id."'  AND user_comment.Status=0 AND user_comment.Comment_Status=1");
$comv->execute();
$fimg=$conn->prepare("SELECT Image_Name From Images WHERE Id='".$pic_id."'");
$fimg->execute();
$gimg=$fimg->fetch(PDO::FETCH_OBJ);
$pro=$conn->prepare("SELECT Profile_Picture FROM user_registration WHERE User_Name='".$_SESSION['user']."'");
$pro->execute();
$prof_img=$pro->fetch(PDO::FETCH_OBJ);
$smt=$conn->prepare("SELECT * FROM post,images WHERE post.Id = images.Id AND post.Id='".$pic_id."'");
$smt->execute();
$qry=$conn->prepare("SELECT COUNT(Total_Comment) AS comment FROM user_comment WHERE Status=0 AND Image_Id ='".$pic_id."'");
$qry->execute();
$result=$qry->fetch(PDO::FETCH_OBJ );
$total_coment=$result->comment;
?>
<?php include 'header.php';?>

<?php include 'nav.php';?>

<div class="container-fluid">
    <?php include 'right_sidebar.php';?>
<div class="col-md-1"></div>

    <div class="col-md-9 main-container-top container">
<?php while($rows=$smt->fetch(PDO::FETCH_OBJ)):?>
        <div class="media col-md-12 description-post">


           <img src="image/<?php echo $rows->Image_Name;?>" alt="<?php echo $rows->Image_Name;?>" class="img-rounded img-responsive media-left img-description"/>
            <div class="media-body">
               <h4 class="h4 description-heading"><?php echo $rows->Post_Title;?> <small class="pull-right"> <?php echo $total_coment;?></small></h4>
                <p class="post-text text-justify text-info">

                    <?php echo $rows->Post;?>
            </div>
<?php endwhile;?>
        </div>

        <br/>

            <div class="media col-md-12 comment-section">
<?php
if(isset($_SESSION['comment-error']))
{
?>
    <span class="alert alert-warning col-md-6 container col-md-offset-3"><?php echo $_SESSION['comment-error']; ?></span>
    <?php
}
unset($_SESSION['comment-error']);
 if(isset($_SESSION['comment-success']))
{
?>
    <span class="alert alert-success col-md-6 container col-md-offset-3"><?php echo $_SESSION['comment-success']; ?></span>
    <?php
}
unset($_SESSION['comment-success']);
    ?>
                <br/>



                <?php while($gcom=$comv->fetch(PDO::FETCH_OBJ)):?>
                <span class="col-md-1 comment-pic"><img src="profile%20picture/<?php echo $gcom->Profile_Picture;?>" alt="post image" class="img-thumbnail img-responsive comment-img"/></span>
                <div class="media-body  comment-head col-md-10">
                    <h6 class="h6"><a href="#"> <?php echo $gcom->User_Name;?> </a> on <?php echo $gcom->On_Time;?></h6>
                    <p class="comment"><?php echo $gcom->Comment;?></p>
                </div>
                    <?php endwhile;?>
            </div>

        <br/>
        <div class="col-md-12 container">
            <h4 class="description-heading h4 text-muted">Share your thought</h4>
            <br/>
            <form action="comment.php" method="post" class="col-md-12" id="commentForm">
                <textarea name="comment" id="" cols="100" rows="5" placeholder="Your comment"></textarea>
                <input type="hidden" name='picture-name' value="<?php echo $gimg->Image_Name;?>"/>
                <input type="hidden" name="profile-pic" value="<?php echo $prof_img->Profile_Picture;?>"/>
                <input type="hidden" name="pic-id" value="<?php echo $pic_id;?>"/>
                <input type="hidden" name="image-id" value="<?php echo '?id='.$pic_id;?>"/>
                <br/>
                <input type="submit" value="Post" name="cmsg" id="" class="btn btn-info"/>
                <br/>
            </form>
        </div>
        <br/>



</div>

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