我有一个论坛,用户可以在其中发布问题,并可以投赞成票和反对票。
我想了解每个帖子的upvote
和downvote
。
我以前所做的是在 3 组查询中执行此操作。
$data = mysqli_query($con,"select * from posts");
while($row = mysqli_fetch_assoc($data)){
$pid = $row['post_id'];
$up = mysqli_fetch_assoc(mysqli_query("SELECT COUNT(*) as c FROM up WHERE post_id = $pid"))['c'];
$down = mysqli_fetch_assoc(mysqli_query("SELECT COUNT(*) as c FROM down WHERE post_id = $pid"))['c'];
}
谁能告诉我如何在一个查询中做这些事情,因为如果在第一个查询中得到很多posts
,那么就会有很多查询要做。
您可以使用子查询并将所有内容放在第一个查询中。
这可能是一个好的开始:
$data = mysqli_query($con, "select posts.*, " .
"(SELECT COUNT(*) FROM up WHERE post_id = posts.post_id) as totalUp, " .
"(SELECT COUNT(*) FROM down WHERE post_id = posts.post_id) as totalDown " .
"from posts");
while($row = mysqli_fetch_assoc($data)){
// ...
}
您可以使用相关的子查询,其中 赞成票反对票根据帖子 ID 计算
SELECT p.*,
( select count(*) from up where post_id = p.post_id ) as upVotesCount,
( select count(*) from down where post_id = p.post_id ) as downVotesCount,
FROM posts p