Retrieving data from MySQL in particular order

Question

I don't know how to ask this question but I'll try my best. I am designing a Comment and Reply system such that each comment has a particular number of replies and the comments and replay are stored separately in two tables in the same MySQl database, so we have to two tables

Table 1

Comment ID | Comment | Date

Table 2

X | Reply Text | Date

where X represents the comment's ID the reply belongs to.

I know that the code

$res=mysql_query("SELECT * from commenttable ORDER BY `Date` DESC ");
while($row=mysql_fetch_array($res))
{
echo $row["Comment"];
}

lists the comments from recent to old.

Is it possible to modify this code to list the comments from the one with biggest number of replies to least.

To answer your question, yes it is possible. – asprin May 21 '14 at 07:55 — asprin, May 21 '14 at 07:55

score 0 · Answer 1 · edited May 23 '17 at 11:50

0

You could run a "count" query on the replies table, and order by that. I'm no SQL expert so I'm sure this can be improved in multiple ways, but I hope this will set you in the right direction:

Select commentId, count(1) as c 
from replytable
group by commentId
order by c;

should get you the number of replies for each commentId, and you can then grab the comments' data from the comments table according to ID.

further reading: How to use count and group by at the same select statement http://www.w3resource.com/sql/aggregate-functions/count-with-group-by.php

edited May 23 '17 at 11:50

Community

1
1

answered May 21 '14 at 08:01

ayeletd

361
4
7

Is it possible to list comments with zero replies. – MrDi May 22 '14 at 13:30
It should be possible. One (probably inefficient) way to do this would be `select * from comments where commentid in (select commentId from comments MINUS select distinct commentId from Replies)` – ayeletd May 24 '14 at 07:15

score 0 · Answer 2 · answered May 21 '14 at 08:02

0

I believe count() would be the answer to your question. But before we proceed to counting the amount of reply you have on the second table, you might want to join the two tables. You can use ORDER BY count(id)

answered May 21 '14 at 08:02

John Guan

744
2
11
26

Bojan Kovacevic · Answer 3 · 2014-05-23T06:57:28.980

0

something like this should work:

select t1.comment_id, count(t2.x) 
FROM table1 t1 LEFT JOIN table2 t2 
ON t1.comment_id=t2.x
GROUP BY t1.comment_id
ORDER BY 2 DESC

edited May 23 '14 at 06:57

answered May 21 '14 at 08:05

Bojan Kovacevic

778
7
19

May need to use a LEFT or RIGHT JOIN in case there are no records for t1.comment_id in t2 – mstrthealias May 21 '14 at 08:20
maybe, but inner join make more sense. because you probably want question that have some replies. Although theoretically left join is possible. right join is not possible,as x is foreign key and you cant reply to non existing comment. [EDIT] left join do make sense when you think about it. – Bojan Kovacevic May 21 '14 at 08:22
What is the difference between t1 and table1 – MrDi May 22 '14 at 10:37
t1 is alias for table1. shorter way to access it. It is not needed, just shorten the code. I could use table1.comment_id = table2.x in ON clause too. Remember, if you want to use alias for table you should define it in FROM clause. – Bojan Kovacevic May 22 '14 at 10:39
Bojan, what is "FROM table1 t1 INNER JOIN table2 t2", what does table1 t1 stand for? – MrDi May 22 '14 at 10:51
i just told you, t1 is alias for table1 and t2 is alias for table2. read about [table alias - lower part of the page](http://www.mysqltutorial.org/mysql-alias/) – Bojan Kovacevic May 22 '14 at 11:32
Thanks Bojan, I get it now. Is it possible to list the comments with no replies too. – MrDi May 22 '14 at 13:48
@user28952 yes,just replace INNER JOIN with LEFT JOIN and you are good to go. I just updated my answer so it fit your need. – Bojan Kovacevic May 23 '14 at 06:56

Retrieving data from MySQL in particular order

3 Answers3