CODEWITHSUNDEEP
phpmysqldatabasepdo
Johnny Catsville
Johnny Catsville

Reputation: 55

PHP, PDO Building right query for displaying most popular topics

I almost made it by unfortunately I stuck and spent hours on unsuccessful attempts to make it work right :(

So, I'm making a forum/blog. Users can create topics and discuss them with the comments. And I wanna display most popular topics (7 for example), those that have most comments (will be perfect to make it show most popular topics for last week or something). The problem is my DB structure, topics and user_comments are different tables. Here is how it is approximately like:

topics:
- id
- title
- date

user_comments:
-id
-topic_id
-comment
-date

topic_id in user_comments is used to define to which topic comments belong. What I'm trying is to select all topic_id from user_comments that have most comments of all and use it to display title of the most popular topics. Here is my code example:

$stmt = $db->prepare('SELECT topic_id FROM user_comments ORDER BY comment DESC LIMIT 7');
$stmt->execute();
$topicsID = $stmt->fetchAll();

foreach ($topicsID as $row) {
  // Problem is starting here, it displays topic_id multiple times
  echo 'Most popular topic is: ' . $row['topic_id'] . '<br>';
  $topic_id = $row['topic_id'];
}
  $stmt = $db->prepare('SELECT title FROM topics WHERE id = :topic_id');
  $stmt->bindValue(':topic_id', $topic_id);
  $stmt->execute();
  $title = $stmt->fetchAll();

  foreach ($title as $row) {
    echo 'Most popular topic title is: ' . $row['title'] . '<br>';
  }

At the moment it displays every topic multiple times (like one time per comment or something). I will be very happy for any help, thanks everyone!

Upvotes: 1

Views: 120

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

you could obtain with a single query with join .. count and group by

  SELECT t.title, count(*) 
  FROM topics t 
  inner join user_comments  u on u.topic_id = t.id
  GROUP BY t.title
  ORDER BY count(*)  DESC 
  LIMIT 7

Upvotes: 2

Related Questions