CODEWITHSUNDEEP
phpmysqlpdo
Ioannis P.
Ioannis P.

Reputation: 131

What PDO query should i use?

I have a table on my database which called topics(topic_id,topic_subject,topic_content,topic_date,topic_cat,topic_comm,topic_by,votes,tags,views,flags), from this table i want to echo 10 top users(topic_by) with the most topics(max number of topics) on a specific community(topic_comm). What PDO query should i use ? I think need something like below:

$sql = $conn -> prepare("SELECT topic_by WHERE topic_comm=:comm AND ( MAX COUNT(topic_id) )")

Upvotes: 1

Views: 39

Answers (1)

invisal
invisal

Reputation: 11171

Let construct the query step by step. In order to get the top 10 user by most topic by community, you need to compute number of topic by each user on that community.

SELECT topic_by, COUNT(*) AS total_topic FROM topic 
WHERE topic_comm = :comm GROUP BY topic_by

Then, you want to get the top 10 user, you can use basic subquery and LIMIT. So the final query

SELECT * FROM (
  SELECT topic_by, COUNT(*) AS total_topic FROM topic 
  WHERE topic_comm = :comm GROUP BY topic_by
) AS t ORDER BY t.total_topic DESC LIMIT 10

Upvotes: 3

Related Questions