Donutdude420 Donutdude420 - 1 year ago 52
SQL Question

Need to sort results by amount of replies in other table SQL

I need to retreive data from 3 tables:

The thread, the poster, and the amount of replies to that post.

I have made it work like this but I can't see an option to sort it by the most replies.

$_result= $_PDO->query("
SELECT * FROM t_users, t_forum
WHERE t_forum.d_op= t_users.d_user_id

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.

Answer Source

Your query should look something like:

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