MySQL SELECT Statement Only Returns One Row

Question

It's been quite a long time since I last asked a question, but I've been stumped messing with SQL in an attempt to build my own forum system.

The background of my problem is that I'm trying to create a function to display the most popular threads based on how many replies it has and how recent the latest reply 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 PDO's fetchAll() function to bring the results into a variable.

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

And, in an effort to trouble shoot, I use var_dump() on $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 would it not be returning more than one row, and how do I make it do so?

Community · Accepted Answer · 2023-11-17 20:42:18Z

You are on the right track with the joined subquery, but you need to apply the COUNT() inside the subquery. Otherwise, the aggregate group is applied to the outer query, resulting in one row.

This is because you have no GROUP BY, which MySQL is lenient about. It gives you one row back with not quite the results you expect, where most other RDBMS would give you a parse error. Otherwise, an aggregate function (COUNT(),MIN(),MAX(),SUM(), etc) with no GROUP BY will never result in more than one row returned.

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

A quick note about PDO and prepare(): While it is a good thing that it's in your workflow, it is not strictly necessary to use prepare()/execute() if your query has no input values (no dynamic WHERE clause, for example). For a static query in a case like this, you can safely call PDO::query() with less overhead and a little less code.

Collectives™ on Stack Overflow

MySQL SELECT Statement Only Returns One Row

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related