MySQL SELECT statement returns only one row

Question

It has been quite a while since the last time I asked a question, but I was at a standstill, encountering SQL, trying to create my own forum system.

The background of my problem is that I am trying to create a function to display the most popular threads based on how many answers she has and how recent the last answer was.

With some reading, I made this SQL statement.

SELECT 
topics.id, 
topics.authorID,
topics.lastReply
FROM 
ms_topics as topics
INNER JOIN
(SELECT inTopic FROM ms_posts) as rcount
ON
rcount.inTopic = topics.id
ORDER BY
topics.lastReply DESC,
count(rcount.inTopic)

And then I use the PDO function fetchAll()to cast the results into a variable.

$GetPopular = $db->prepare('SQL Statement Above');
$GetPopular->execute();
$Populars = $GetPopular->fetchAll();

And, trying to solve the problem, I use var_dump()in $populars, which returns only one row from the database.

array(1) { [0]=> array(3) { ["id"]=> string(1) "4" ["authorID"]=> string(1) "1" ["lastReply"]=> string(19) "2014-06-08 19:49:50" } }

My question is basically this; why not return more than one row, and how do I do this?

Answer 1

, COUNT() . , .

, GROUP BY, MySQL . , . (COUNT(),MIN(),MAX(),SUM() ..) GROUP BY .

SELECT 
  topics.id, 
  topics.authorID,
  topics.lastReply,
  postsInTopic
FROM 
  ms_topics as topics
  /* The subquery provides the aggregate count
     grouped per inTopic */
  INNER JOIN (
    SELECT
     inTopic,
     COUNT(*) AS postsInTopic
   FROM ms_posts
   GROUP BY inTopic
  ) as rcount ON rcount.inTopic = topics.id
ORDER BY
  topics.lastReply DESC,
  /* Use the alias from the subquery postsInTopic to srot */
  postsInTopic

PDO prepare(): , prepare()/execute(), ( WHERE ). PDO::query() .