Select query in MySQL

Question

I want to select total comments posted on post. Somehow I am able to do it but the problem is that the total comment will show under each post even if there is no comment on the post.

Problem: Not able to get the comment related to post.

What I want :

To select total comment related to 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 

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

Both the queries return all (total) comment on all posts but I want only show comment related to specific post.

My post table

My Comment table

Result look like

OR

But i want like

OR

UPDATE:

I think that most of people don't understand what i am trying to ask? so i am posting the index.php code, where i access data from data base using while loop, and on the index.php page i want to access the total comment posted on specific post.

Index.php

<?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");
$smtt->execute();
$myres=$smtt->fetch(PDO::FETCH_OBJ);
$imgid=$myres->Id;
$imgname=$myres->File_Name;
$qry=$conn->prepare("SELECT SUM(Total_Comment) AS comment FROM user_comment WHERE Image_Id='".$imgid."' AND Image_Name='".$imgname."'");
$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';?>

Update 2

i know the reason that why the query show 2or 3 under each post and the reason is that the query select first or second id from table and count/sum it, but i am unable to resolve the issue, because i want total comment under each post, not one id comments under all post..

Note:

When i run the query on post-description page then it will work fine, but i want to show the number of total comment on each post on index.php page...

My post-description.php code

<?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"><i class="fa fa-comments-o fa-2x"></i> <?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';?>

Answer 1

If you are running a separate query for each post you've returned, which is what it looks like you're trying to do, we're just guessing based on the two queries you posted, which both return a single row...

To use that pattern, you'd need to add an additional predicate to the WHERE clause to identify which post you are wanting to return the comment count for. For example:

SELECT SUM(c.total_comment) AS comment
  FROM user_comment c
 WHERE c.status = 0 
   AND c.image_id = 2

If there are no rows for a given image_id, the SUM() aggregate is going to return NULL. You can either handle that in your code, and convert that to a zero, or you can handle that in the SQL, so that it will return zero rather than NULL. You can use the convenient IFULL() function...

SELECT IFNULL(SUM(c.total_comment),0) AS comment
  FROM user_comment c
 WHERE c.status = 0 
   AND c.image_id = 2

If the first argument of the IFNULL function is NULL, the function returns the second argument, otherwise it returns the first.

IFNULL(a,b) 

is MySQL-specific shorthand for the ANSI

CASE WHEN a IS NULL THEN b ELSE a END

That answers the question you were asking. How to get a count for a specific post.

---

The other answers here are addressing a problem with this approach... running a separate query for each post is NOT the most efficient or the most scalable design.

Each query execution requires a roundtrip to the database, the database has to parse the SQL text, check that the syntax is valid, do a semantic check, dictionary lookups to verify that the identifiers are valid, the database user has the required privileges, decide on an execution plan, and then run the query, prepare a resultset and return to the caller. And that overhead adds up in a tight loop.

Some of the other answers here are addressing. Rather than running a separate query for each image_id, they get the counts in one fell swoop. They do this by adding the image_id column to the SELECT list and add a GROUP BY image_id to the query... to return a result like this

image_id  comment
--------  -------
       2        3
       5        2

That would get you back all of the non-zero counts. BUT... you wouldn't run this in the loop that's fetching the rows from post table... you'd run this query first, and store the results into an array, and then for each post that you're putting on the page, you'd do an array lookup to find the count. If one isn't found, then the count is zero.

You could also return zero counts, and store those as well, with a query like this:

SELECT p.id
     , IFNULL(SUM(total_comment),0) AS comment 
 FROM post p
 LEFT
 JOIN user_comment c 
   ON c.image_id = p.id
  AND c.status = 0
GROUP BY p.id

But that would just be extra rows with zero, still an array lookup, the only difference is you would be finding a match... really no different.

BUT... you don't really want to do that at all.

What the query really hints at is the idea that you don't need a SEPRATE query to get the comment count; you can get that in the SAME QUERY that returns the rows from post.

Just add the outer join to the user_comment table, add a GROUP BY clause on the unique identifier in the post table, and return that aggregate SUM() expression to the SELECT list, along with the columns you are turning from the post table. For example:

SELECT p.id
     , p.Post_Title
     , p.Post
     , p.Filename
     , p.Tag
     , p.Post_Date
     , p.Post_By
     , IFNULL(SUM(c.total_comment),0) AS comment 
 FROM post p
 LEFT
 JOIN user_comment c 
   ON c.image_id = p.id
  AND c.status = 0
GROUP BY p.id
ORDER BY p.Post_Date DESC, p.id DESC

That's usually a much more scalable design, to return the results in a single query, rather than running a boatload of separate queries.

As another alternative, usually not quite as efficient as the outer join, is to use a correlated subquery in the SELECT list:

SELECT p.id
     , p.Post_Title
     , p.Post
     , p.Filename
     , p.Tag
     , p.Post_Date
     , p.Post_By
     , ( SELECT IFNULL(SUM(c.total_comment),0)
           FROM user_comment c
          WHERE c.image_id = p.id
            AND c.status = 0
       ) AS comment
 FROM post p
ORDER BY p.Post_Date DESC, p.id DESC

Or, we could also use an outer join operation to an inline view (but this is more applicable in the more general case, when there are more joins involved, and we don't want to double or triple count, though it does return the same result in this case (assuming that id is unique in the post table,)

SELECT p.id
     , p.Post_Title
     , p.Post
     , p.Filename
     , p.Tag
     , p.Post_Date
     , p.Post_By
     , IFNULL(t.total_comment,0) AS comment
  FROM post p
  LEFT
  JOIN ( SELECT c.image_id 
              , SUM(c.total_comment) AS total_comment
           FROM user_comment c
          WHERE c.image_id = p.id
          GROUP BY c.image_id
       ) t
    ON t.image_id = p.id
 ORDER BY p.Post_Date DESC, p.id DESC

---

FOLLOWUP

Based on the PHP code you posted, the point is... you only need a SINGLE SQL statement.

Just return the derived "total_comment" value for each post, along with each post row, just modify the query (as I indicated in my answer above, there's several possible ways to get that result. Just tell the database to return that count to you. Then your code is much more straightforward. (And for a more deterministic result, add an ORDER BY clause to the query.)

For example:

$sql="SELECT p.Id
           , p.Post_Title
           , p.Post
           , p.Filename
           , p.Post_Date
           , p.Post_By
           , IFNULL(SUM(c.total_comment),0) AS total_comment
        FROM post p
        LEFT
        JOIN user_comment c 
          ON c.image_id = p.id
         AND c.status = 0
       GROUP BY p.id
       ORDER BY p.Post_Date DESC, p.id DESC"; 

$stmt=$conn->prepare($sql);
$stmt->execute();

while($post = $stmt->fetch(PDO::FETCH_OBJ)):
    echo "<hr>";
    echo "<br>" . $post->Id;
    echo "<br>" . $post->Post_Title;
    echo "<br>" . $post->Post;
    echo "<br>" . $post->Filename;
    echo "<br>" . $post->Post_Date;
    echo "<br>" . $post->Post_By;
    echo "<br>" . $post->total_comment;
endwhile;

This is a very simple example that just echos out the values returned, without all the HTML you actually want to output. It serves to demonstrate that you don't need to muck up your code with a a second or third SQL statement.

And (obviously) this example doesn't address escaping the potential HTML characters stored in the database columns, and doesn't address PDO error handling, either setAttribute(PDO::ATTR_ERRORMODE,PDO::ERRMODE_EXCEPTION) and appropriate try/catch/finally blocks, or adding individual checks of the return from the prepare and execute calls. Those are omitted to avoid obfuscating the pattern.

Answer 2

SELECT SUM(Total_Comment) AS comment FROM user_comment INNER JOIN post ON user_comment.Image_Id = post.Id WHERE Status=0 GROUP BY post.Id

I believe group by with inner join should help you get what you want. Hope this helps.