MySQL query (with JOIN) only retrieving the first row

Question

Here is my query

$getPosts = mysql_query ('SELECT `posts`.*, COUNT(`comments`.`comment_id`) 
                          FROM `posts` 
                          LEFT JOIN `comments` 
                          ON (`posts`.`post_id` = `comments`.`post_id`) 
                          ORDER BY `posts`.`post_id` DESC')
            or die(mysql_error());

and then to loop through it...

while ($post = mysql_fetch_row($getPosts))
{
    echo $post[1] . ' ' . $post[4] . ' comments'; // example
}

it all works fine, but only for the first row.

now, post_id 1 has 2 comments. post_id 2 has no comments in the DB.

I assume this is how JOIN works, but I don't understand. I think LEFT JOIN only matches what's on the left (so post_id) but I tried INNER JOIN, OUTER JOIN, etc.. and it doesn't work

update: I tried FULL JOIN (after checking w3schools again), and it says "Unknown table 'posts'" — cantsay, Mar 10 '13 at 02:22
[Please, don't use mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in new code. They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)? Learn about [_prepared statements_](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which. — TNK, Mar 10 '13 at 02:22

Michael Berkowski · Accepted Answer · 2013-03-10T02:48:54.740

Your COUNT() aggregate must be used with a GROUP BY clause. MySQL will return results without it, but they won't be the result you expect, as it will return one related row, indeterminately.

SELECT
  `posts`.*, 
  `c`.`num_comments`
FROM
  `posts` 
  /* join against a subquery which retrieves the number of comments per post_id */
  LEFT JOIN (
    SELECT `post_id`, COUNT(*) AS `num_comments` 
    FROM `comments`
    GROUP BY `post_id` 
  ) AS c ON (`posts`.`post_id` = `c`.`post_id`) 
ORDER BY
  `posts`.`post_id` DESC

MySQL will probably allow you to simplify this without the subquery, since it won't require every column from posts to be listed in the GROUP BY, but this isn't recommended as it won't work everywhere.

/* MySQL only, not recommended */
SELECT
  posts.*,
  COUNT(comments.comment_id) AS num_comments
FROM 
  posts
  LEFT JOIN comments ON posts.post_id = comments.post_id
GROUP BY posts.post_id

Otherwise, you will need to list every column from posts in the GROUP BY since they are all the SELECT. This will work because the join involves only the one to many relationship posts to comments.

When you fetch this row in PHP, use the alias num_comments. It is recommended to use mysql_fetch_assoc(), and access columns by their name rather than a numeric key.

while ($post = mysql_fetch_assoc($getPosts))
{
    echo $post['post_id'] . ' ' . $post['num_comments'] . ' comments';
}

In the long run, as noted in the comments above, consider switching to a newer API such as MySQLi or PDO, as the mysql_*() extension is facing deprecation in the next major PHP verison.

worked perfect. thanks. is there any way to do this without aliases though (just curious as I'm still learning about formatting queries). thanks — cantsay, Mar 10 '13 at 02:55
@cantsay you don't need to alias the table `comments`, I just did it as `c` to save typing. You can skip the alias on the `COUNT()` too, but then you have to specify it the same way in the array key when you fetch it. It is far more readable to use the alias. — Michael Berkowski, Mar 10 '13 at 03:04

MySQL query (with JOIN) only retrieving the first row

1 Answers1