title: Select query in MySQL
body: <p>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.</p>
  
  <p><strong>Problem:</strong> Not able to get the comment related to post.</p>
  
  <p><strong>What I want :</strong> </p>
  
  <p>To select total comment related to each post.</p>
  
  <p><strong>What I try :</strong></p>
  
  <pre><code>SELECT SUM(total_comment) AS comment 
  FROM   user_comment 
         INNER JOIN post 
                 ON user_comment.image_id = post.id 
  WHERE  status = 0 
  </code></pre>
  
      
  
  <pre><code>SELECT SUM(total_comment) AS comment 
  FROM   user_comment 
  WHERE  status = 0 
  </code></pre>
  
  <p>Both the queries return all (total) comment on all posts but I want only show comment related to specific post.</p>
  
  <p><strong><a href="https://i.stack.imgur.com/k0ERG.png" rel="nofollow noreferrer">My post table</a></strong></p>
  
  <p><img src="https://i.stack.imgur.com/k0ERG.png" alt="enter image description here"></p>
  
  <p><strong><a href="https://i.stack.imgur.com/3tmVC.png" rel="nofollow noreferrer">My Comment table</a></strong></p>
  
  <p><img src="https://i.stack.imgur.com/3tmVC.png" alt="enter image description here"></p>
  
  <p>Result look like
  <img src="https://i.stack.imgur.com/jmkOk.png" alt="enter image description here"></p>
  
  <p>OR</p>
  
  <p><img src="https://i.stack.imgur.com/76HoY.png" alt="enter image description here"></p>
  
  <p>But i want like</p>
  
  <p><img src="https://i.stack.imgur.com/GG4D7.png" alt="enter image description here"></p>
  
  <p>OR
  <img src="https://i.stack.imgur.com/mdeFq.png" alt="enter image description here"></p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>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.</p>
  
  <p><strong>Index.php</strong></p>
  
  <pre><code>&lt;?php
  session_start();
  include 'conn.php';
  if(!isset($_SESSION['user']))
  {
      header('location:signin.php');
  }
  
  
  
  $smt=$conn-&gt;prepare("SELECT * FROM post,images WHERE post.Id=images.Id");
  $smt-&gt;execute();
  $smtt=$conn-&gt;prepare("SELECT * FROM post");
  $smtt-&gt;execute();
  $myres=$smtt-&gt;fetch(PDO::FETCH_OBJ);
  $imgid=$myres-&gt;Id;
  $imgname=$myres-&gt;File_Name;
  $qry=$conn-&gt;prepare("SELECT SUM(Total_Comment) AS comment FROM user_comment WHERE Image_Id='".$imgid."' AND Image_Name='".$imgname."'");
  $qry-&gt;execute();
  $result=$qry-&gt;fetch(PDO::FETCH_OBJ );
  $total_coment=$result-&gt;comment;
  ?&gt;
  &lt;?php include 'header.php';?&gt;
  &lt;?php
  if(isset($_SESSION['user']))
  {
      include 'nav.php';
  }
  else
  {
      include 'nav-simple.php';
  }
  ?&gt;
  
      &lt;div class="container-fluid"&gt;
      &lt;?php include 'right_sidebar.php';?&gt;
  &lt;div class="main-container-top" id="masonry-grid"&gt;
      &lt;?php while($rows=$smt-&gt;fetch(PDO::FETCH_OBJ)): ?&gt;
  
          &lt;div class="col-md-3 grid-item post-col"&gt;
  &lt;img src="image/&lt;?php echo $rows-&gt;Image_Name;?&gt;" data-echo="image/&lt;?php echo $rows-&gt;Image_Name;?&gt;" class="post-image"/&gt;
  
              &lt;h5&gt;Post On &amp;nbsp;&lt;?php echo $rows-&gt;Post_Date;?&gt; &lt;span class="pull-right"&gt;By &lt;?php echo $rows-&gt;Post_By; ?&gt;&lt;/span&gt; &lt;span class="pull-right"&gt;Total Comment &lt;?php echo $total_coment;?&gt;&lt;/span&gt;&lt;/h5&gt;
  
              &lt;a href="post-description.php?id=&lt;?php echo $rows-&gt;Id ?&gt;"&gt; &lt;h4&gt;&lt;?php echo $rows-&gt;Post_Title;?&gt;&lt;/h4&gt; &lt;/a&gt;
              &lt;p&gt;
                  &lt;?php echo $rows-&gt;Post;?&gt;
              &lt;/p&gt;
  
          &lt;/div&gt;
  
  
      &lt;?php endwhile;?&gt;
  &lt;/div&gt;
      &lt;/div&gt;
  
  &lt;?php include 'footer-content.php';?&gt;
  &lt;?php include 'footer.php';?&gt;
  </code></pre>
  
  <p><strong>Update 2</strong></p>
  
  <p><em>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..</em></p>
  
  <p><strong>Note:</strong></p>
  
  <p>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...</p>
  
  <p><strong>My post-description.php code</strong></p>
  
  <pre><code>&lt;?php
  session_start();
  include 'conn.php';
  $pic_id='';
  if(isset($_GET['id']))
  {
      $pic_id=$_GET['id'];
  }
  $comv=$conn-&gt;prepare("SELECT * FROM user_comment WHERE user_comment.Image_Id='".$pic_id."'  AND user_comment.Status=0 AND user_comment.Comment_Status=1");
  $comv-&gt;execute();
  $fimg=$conn-&gt;prepare("SELECT Image_Name From Images WHERE Id='".$pic_id."'");
  $fimg-&gt;execute();
  $gimg=$fimg-&gt;fetch(PDO::FETCH_OBJ);
  $pro=$conn-&gt;prepare("SELECT Profile_Picture FROM user_registration WHERE User_Name='".$_SESSION['user']."'");
  $pro-&gt;execute();
  $prof_img=$pro-&gt;fetch(PDO::FETCH_OBJ);
  $smt=$conn-&gt;prepare("SELECT * FROM post,images WHERE post.Id = images.Id AND post.Id='".$pic_id."'");
  $smt-&gt;execute();
  $qry=$conn-&gt;prepare("SELECT COUNT(Total_Comment) AS comment FROM user_comment WHERE Status=0 AND Image_Id ='".$pic_id."'");
  $qry-&gt;execute();
  $result=$qry-&gt;fetch(PDO::FETCH_OBJ );
  $total_coment=$result-&gt;comment;
  ?&gt;
  &lt;?php include 'header.php';?&gt;
  
  &lt;?php include 'nav.php';?&gt;
  
  &lt;div class="container-fluid"&gt;
      &lt;?php include 'right_sidebar.php';?&gt;
  &lt;div class="col-md-1"&gt;&lt;/div&gt;
  
      &lt;div class="col-md-9 main-container-top container"&gt;
  &lt;?php while($rows=$smt-&gt;fetch(PDO::FETCH_OBJ)):?&gt;
          &lt;div class="media col-md-12 description-post"&gt;
  
  
             &lt;img src="image/&lt;?php echo $rows-&gt;Image_Name;?&gt;" alt="&lt;?php echo $rows-&gt;Image_Name;?&gt;" class="img-rounded img-responsive media-left img-description"/&gt;
              &lt;div class="media-body"&gt;
                 &lt;h4 class="h4 description-heading"&gt;&lt;?php echo $rows-&gt;Post_Title;?&gt; &lt;small class="pull-right"&gt;&lt;i class="fa fa-comments-o fa-2x"&gt;&lt;/i&gt; &lt;?php echo $total_coment;?&gt;&lt;/small&gt;&lt;/h4&gt;
                  &lt;p class="post-text text-justify text-info"&gt;
  
                      &lt;?php echo $rows-&gt;Post;?&gt;
              &lt;/div&gt;
  &lt;?php endwhile;?&gt;
          &lt;/div&gt;
  
          &lt;br/&gt;
  
              &lt;div class="media col-md-12 comment-section"&gt;
  &lt;?php
  if(isset($_SESSION['comment-error']))
  {
  ?&gt;
      &lt;span class="alert alert-warning col-md-6 container col-md-offset-3"&gt;&lt;?php echo $_SESSION['comment-error']; ?&gt;&lt;/span&gt;
      &lt;?php
  }
  unset($_SESSION['comment-error']);
   if(isset($_SESSION['comment-success']))
  {
  ?&gt;
      &lt;span class="alert alert-success col-md-6 container col-md-offset-3"&gt;&lt;?php echo $_SESSION['comment-success']; ?&gt;&lt;/span&gt;
      &lt;?php
  }
  unset($_SESSION['comment-success']);
      ?&gt;
                  &lt;br/&gt;
  
  
  
                  &lt;?php while($gcom=$comv-&gt;fetch(PDO::FETCH_OBJ)):?&gt;
                  &lt;span class="col-md-1 comment-pic"&gt;&lt;img src="profile%20picture/&lt;?php echo $gcom-&gt;Profile_Picture;?&gt;" alt="post image" class="img-thumbnail img-responsive comment-img"/&gt;&lt;/span&gt;
                  &lt;div class="media-body  comment-head col-md-10"&gt;
                      &lt;h6 class="h6"&gt;&lt;a href="#"&gt; &lt;?php echo $gcom-&gt;User_Name;?&gt; &lt;/a&gt; on &lt;?php echo $gcom-&gt;On_Time;?&gt;&lt;/h6&gt;
                      &lt;p class="comment"&gt;&lt;?php echo $gcom-&gt;Comment;?&gt;&lt;/p&gt;
                  &lt;/div&gt;
                      &lt;?php endwhile;?&gt;
              &lt;/div&gt;
  
          &lt;br/&gt;
          &lt;div class="col-md-12 container"&gt;
              &lt;h4 class="description-heading h4 text-muted"&gt;Share your thought&lt;/h4&gt;
              &lt;br/&gt;
              &lt;form action="comment.php" method="post" class="col-md-12" id="commentForm"&gt;
                  &lt;textarea name="comment" id="" cols="100" rows="5" placeholder="Your comment"&gt;&lt;/textarea&gt;
                  &lt;input type="hidden" name='picture-name' value="&lt;?php echo $gimg-&gt;Image_Name;?&gt;"/&gt;
                  &lt;input type="hidden" name="profile-pic" value="&lt;?php echo $prof_img-&gt;Profile_Picture;?&gt;"/&gt;
                  &lt;input type="hidden" name="pic-id" value="&lt;?php echo $pic_id;?&gt;"/&gt;
                  &lt;input type="hidden" name="image-id" value="&lt;?php echo '?id='.$pic_id;?&gt;"/&gt;
                  &lt;br/&gt;
                  &lt;input type="submit" value="Post" name="cmsg" id="" class="btn btn-info"/&gt;
                  &lt;br/&gt;
              &lt;/form&gt;
          &lt;/div&gt;
          &lt;br/&gt;
  
  
  
  &lt;/div&gt;
  
  &lt;/div&gt;
  &lt;?php include 'footer-content.php';?&gt;
   &lt;?php include 'footer.php';?&gt;
  </code></pre>