mysql - 在 MySQL 中选择查询

Question

我想选择发布在帖子上的评论总数。不知何故，我能够做到，但问题是即使帖子没有评论，总评论也会显示在每个帖子下。

问题: 无法获取与帖子相关的评论。

我想要什么:

选择与每个帖子相关的评论总数。

我的尝试:

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 

这两个查询都返回所有帖子的所有(总)评论，但我只想显示与特定帖子相关的评论。

My post table

My Comment table

结果看起来像

或者

但我想要像

或者

更新:

我想大多数人不明白我想问什么？所以我发布了 index.php 代码，在那里我使用 while 循环访问数据库中的数据，并且在 index.php 页面上我想访问发布在特定帖子上的总评论。

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

更新 2

我知道为什么查询在每个帖子下显示 2 或 3 的原因是查询从表中选择第一个或第二个 id 并对其进行计数/求和，但我无法解决该问题，因为我想要每个帖子下的总评论帖子，不是所有帖子下的一个 id 评论..

注意:

当我在帖子描述页面上运行查询时，它会正常工作，但我想在 index.php 页面上显示每个帖子的评论总数...

我的 post-description.php 代码

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

使用这个查询..

SELECT COUNT(*) FROM user_comment INNER JOIN post ON user_comment.Image_Name = post.File_Name WHERE user_comment.Status = 0 AND user_comment.Image_Name = "your image id here";

查询工作正常，如果不适合您，则只需更改从 mysql 获取数据/总评论的方式即可。