PHP结合MYSQLI实现用户发帖评论数量统计与展示

实现需求 #

• 完成主流论坛个人主页常规功能：展示指定用户发布的帖子、评论等各类内容总数量
• 项目技术栈：PHP + MySQLi

现有post表结构 #

post_id      Primary int(11)     AUTO_INCREMENT  
title        varchar(255)
users_id     int(11)     
content      varchar(500)      
type         int(11)                     
imagepath    varchar(50) 
date_created datetime

初始方案存在的问题 #

最初的实现思路是在post表新增total_post字段，执行INSERT语句创建新帖时将该字段值自增1，但实际运行中即使用户多次发帖，该字段值始终为1，对应发帖功能代码如下：

function createPost($conn, $content, $title, $users_id, $date_created, $type, $total_post){
    $sql = "INSERT INTO post (title, users_id, content, date_created, type, total_post) VALUES (?,?,?,?,?,?);";

    $stmt = mysqli_stmt_init($conn);
 
    if (!mysqli_stmt_prepare($stmt, $sql)){
     header("location: ../home.php?error=stmtfailed");
     exit();
    }

    $mysqltime = date ('Y-m-d H:i:s');
    $total_post++;
    $type;


    mysqli_stmt_bind_param($stmt, "ssssss", $title, $users_id, $content, $mysqltime, $type, $total_post);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);
    header("location: ../home.php?error=noerroronpost");
     exit();
 }

最初编写的profile.php用户信息展示逻辑同样无法正确统计发帖数，代码如下：

$id = $_SESSION["userid"];
        
$stmt = $conn->prepare('SELECT * from post LEFT JOIN users on users.users_id = ? order by post_id DESC;');
$stmt->bind_param('s', $id);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc()){

echo "<div class='userinfo'>";
echo "<h5 id='usernameprofile'>" ."Username: " .$row["users_username"] ."</h5>";
echo "<h5 id='usernameprofile'>" ."Registration date: " .$row["create_datetime"] ."</h5>";
echo "<h5 id='usernameprofile'>" ."Posts: " .$row["post_id"] ."</h5>"; echo "<br>";
echo "</div>";
}

$stmt->close();

最终可运行方案 #

无需在post表中冗余存储total_post字段，直接通过SQL的COUNT()聚合函数统计对应用户的帖子总数即可，调整后可正常运行的代码如下：

$id = $_SESSION["userid"];   
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$stmt = $conn->prepare('SELECT COUNT(p.post_id) as total_posts from post p INNER JOIN users ON users.users_id = p.users_id WHERE p.users_id = ?;');
$stmt->bind_param('s', $id);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc()){
  $count = $row['total_posts'];

echo "<div class='usertotalpost'>";
echo "<h5 id='totalposts'>" ."Posts: " .$count ."</h5>"; echo "<br>";
echo "</div>";
}

$stmt->close();

上述统计查询方案由网友ADyson提供，经小幅参数调整后即可正常实现发帖数量统计。

内容的提问来源于stack exchange，提问作者Hamodi coolguy