CODEWITHSUNDEEP
phpsqlpdo
SirMaxime
SirMaxime

Reputation: 154

Order posts by amount of comments

I am trying to make a simple search system with some filters, one of these filters is to order them by 'popularity' which would basically put posts with the most comments first and posts with the least comments last. I have 2 tables, one for posts which contains all the posts (title, content, image, postId) and one for comments which contains all comments (content and postId (to verify to what post the comment is from). My query looks like:

  $search = $db->prepare("SELECT comments.postId, comments.commentId, COUNT(comments.commentId) AS comms, posts.title, posts.postDate, posts.postId, posts.content,
                                 FROM comments, posts, users
                                 WHERE comments.postId = posts.postId AND posts.content LIKE ? OR posts.title LIKE ?
                                 GROUP BY comments.commentId
                                 ORDER BY comms DESC
                                 LIMIT 100");
            $search->execute(array("%$q%", "%$q%"));

To display the search results I use: 

foreach($search as $s) {
?>
  <a href="stream.php?viewPost=<?php echo $s['postId'];?>">
    <h5>
      <?php echo $s['title']; ?>
    </h5>
  </a>
  <small>Posted on <?php echo $s['postDate']; ?> </small>
  <div class="divider"></div>
<?php
}
             ?>

But when I search for something, instead of showing posts with most comments first, it shows 1 post (that actually has the most comments). It will show that post over and over again. If I remove 

GROUP BY comments.commentId

from the query it will only show that post once.

If someone could tell me what I'm doing wrong here, because I really don't see it.

Upvotes: 2

Views: 110

Answers (3)

Raj
Raj

Reputation: 1

SELECT p.postId, p.title, p.postDate, posts.postId, p.content,
       COUNT(c.commentId) AS comms, 
FROM posts p join
     comments c
     ON c.postId = p.PostId
WHERE p.content LIKE ? OR p.title LIKE ?
GROUP BY p.postId
ORDER BY comms DESC
LIMIT 100;

Upvotes: 0

BeetleJuice
BeetleJuice

Reputation: 40916

Remove users from your query. You don't use it:

SELECT COUNT(*) AS comms, comments.postId, comments.commentId, ...
FROM posts JOIN comments ON posts.postId = comments.postId
WHERE posts.content LIKE ? OR posts.title LIKE ?
GROUP BY posts.postId
ORDER BY comms DESC
LIMIT 100

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Here are some observations:

  • Learn to use proper, explicit JOIN syntax.
  • If you want to returns posts, don't group by the comment id.
  • The users table is not used.

The query you want looks more like:

SELECT p.postId, p.title, p.postDate, posts.postId, p.content,
       COUNT(c.commentId) AS comms, 
FROM posts p join
     comments c
     ON c.postId = p.PostId
WHERE p.content LIKE ? OR p.title LIKE ?
GROUP BY p.postId
ORDER BY comms DESC
LIMIT 100;

Upvotes: 1

Related Questions