how to order a values fetched from database??
SELECT * FROM comment;
------------------------------------
| commentid | content | read_unread|
------------------------------------
1 hello Unread
2 hi read
Select * From replies;
//commentid Fk from table " comment " so it means row 1 from table comments has 2 replies
------------------------------------
| repnum | rep_content | commentid |
------------------------------------
1 see ya 1
2 ok 1
i want to show this in a table html/php like this
comment num | Content | replies count
--------------------------------------------------
1 hello 2
2 hi 0
//the "replies count 2" came from the table replies replied to commentid 1 from table comment
how can i show this in a single query sql order by the count of the replies count???
Try this :
SELECT a.commentid, a.content, count(b.repnum) as replies_count
FROM comment a left join replies b on a.commentid = b.commentid
GROUP by a.commentid
You can use JOIN on SQL query :
SELECT c.commentid, c.content, COUNT(r.replies) FROM comment c JOIN replies r ON r.commentid = c.commentID GROUP BY c.commentid, c.content;
More infos here : http://dev.mysql.com/doc/refman/5.7/en/join.html
Try it.
SELECT a.commentid, a.content, count(*) as replies_count
FROM comment a
LEFT JOIN replies b on a.commentid=b.commentid
GROUP BY a.commentid, a.content
Left join and count from replies table is your buddies. Inner join will remove comments those don't yet get any replies so here left join will be required. And count from replies is your requirement so count(*) will not work.
SELECT A.commentid AS comment_num, A.content AS Content, count(B.commentid) as replies_count
FROM comment A
LEFT JOIN replies B on A.commentid=B.commentid
GROUP BY A.commentid, A.content