我需要从三个表中检索数据:t_users
、t_forum
、t_replies
。帖子、海报以及对该帖子的回复数量。
我已经让它这样工作了,但我看不到按回复数量排序的选项。
$_result= $_PDO->query("
SELECT * FROM t_users, t_forum
WHERE t_forum.d_op= t_users.d_user_id
$_orderBy");
if ($_result->rowCount() > 0) {
while($_row = $_result-> fetch(PDO::FETCH_ASSOC)){
$_postid = $_row['d_index'];
//count amount of replies per post
$_replycount = $_PDO ->query("SELECT COUNT(*)
FROM t_replies
WHERE d_reply_id = '$_postid'");
$_count = $_replycount ->fetchColumn();
}
}
//I have tried queries like this :
SELECT t_users.*, t_forum.*, COUNT(d_reply_id)
FROM t_users,t_forum, t_replies
WHERE t_forum.d_op= t_users.d_user_id
AND t_forum.d_index = t_replies.d_reply_id
//but this only retrieves one row and literally counts the d_reply_id value as the
value is numeric, thank you for reading.
您的查询应该看起来像:
SELECT /*put here field of thread and poster*/, COUNT(d_reply_id)
FROM t_users
join t_forum
on t_forum.d_op= t_users.d_user_id
left join t_replies
on t_forum.d_index = t_replies.d_reply_id
group by /*put here field of thread and poster*/
order by COUNT(d_reply_id) desc