CODEWITHSUNDEEP
phpmysql
user1470755
user1470755

Reputation: 41

Sort a MySQL result based on total count of another result

I have tables tbl_posts and tbl_comments with primary keys post_id and comment_id respectively. I tried this code:

$allPosts=mysql_query("
    select c.comment_id, post_id, count(*)
    from post u,
    comments c
    where u.comment_id = c.comment_id
    group by comment_id, post_id
    LIMIT 10
");

but I have no clue what it does. How do I combine two tables so that the total comments determines the order of the listed posts from tbl_posts?

Upvotes: 0

Views: 348

Answers (3)

Jake1164
Jake1164

Reputation: 12369

It looks like you have tables named tbl_comment and tbl_post but your query has them listed as just comment and post. 

select c.comment_id, post_id, count(*) 
from tbl_post u, tbl_comments c 
where u.comment_id = c.comment_id 
group by comment_id, post_id LIMIT 10


$allPosts=mysql_query("select c.comment_id, post_id, count(*) from tbl_post u, tbl_comments c where u.comment_id = c.comment_id group by comment_id, post_id LIMIT 10");

This just fixes the query so it runs, and does not address any content issues you may have, namely the group by on both (what I am guessing) are primary keys.

** EDIT ** To fix the sorting try:

SELECT tbl_post.comment_id, count(*)  
FROM tbl_post, tbl_comments
WHERE tbl_post.comment_id = tbl_comment.comment_id  
GROUP BY comment_id LIMIT 10
ORDER BY count(*)

Upvotes: 1

Kermit
Kermit

Reputation: 34063

Explanation of your SQL:

You are selecting column comment_id from table comments, column post_id from table post using a inner join, grouping by comment_id, post_id, with a limit of 10 results.

I would try:

$allPosts = mysql_query("SELECT * FROM 
(SELECT c.comment_id, u.post_id, COUNT(*) AS 'count' FROM post u 
LEFT JOIN comments c ON c.comment_id = u.comment_id 
GROUP BY c.comment_id, u.post_id)
ORDER BY count DESC LIMIT 10");

Upvotes: 0

jcho360
jcho360

Reputation: 3759

Try this, it's more readable if you separate per lines and work with joins

select c.comment_id, post_id, count(*) 
from post u join comments c 
on u.comment_id = c.comment_id 
group by comment_id, post_id LIMIT 10

Upvotes: 1

Related Questions