PHP While Loop Isn't Running Through All Selected Records From MySQL Database

I have three tables underlying a blog: one for users, one for comments and one for threads. Not all the records retrieved are echoed by PHP and I think it may be to do with NULL values, for example on my memberlist page the following query retrieves data on four users:

SELECT COUNT(Topics.MemberID) AS NumberOfTopics, Users.id, 
    Users.FirstName, Users.LastName, Users.Joined 
FROM Users LEFT JOIN Topics ON Users.id=Topics.MemberID GROUP BY Topics.MemberID

However if the last user(s) at the end of the list has no topics (NumberO开发者_开发百科fTopics = 0) they are not presented. If a user has a post count of 0 but they following record has a post count that isn't 0 the user is shown. Why?

Here is the complete code:

$result = mysql_query("SELECT COUNT(Topics.MemberID) AS NumberOfTopics, ".
                      "Users.id, Users.FirstName, Users.LastName, Users.Joined ".
                      "FROM Users LEFT JOIN Topics ON Users.id=Topics.MemberID ".
                      "GROUP BY Topics.MemberID") 
             or die("Query failed with error: ".mysql_error());

echo "<table border='1'>
<tr>
<th>Name</th>
<th>Joined</th>
<th>No. of posts</th>
</tr>";

while($row = mysql_fetch_assoc($result))
  {
  echo "<tr>";
  echo "<td><a href='/neuro/profile.php?userid=$row[id]'>" . 
       $row['FirstName'] . " " . $row['LastName'] . "</a></td>";
  echo "<td>" . $row['Joined'] . "</td>";
  echo "<td>" . $row['NumberOfTopics'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);

Thanks for reading!

---

I believe the problem is that your GROUP BY will force the elimination of NULLs.

I normally will use subquery in these circumstances simply because it is easier to read and understand:

SELECT (select count(*) from Topics T where T.MemberID = U.id) AS NumberOfTopics,
    Users.id, Users.FirstName, Users.LastName, Users.Joined 
FROM Users u

---

I think it's because of your group by.

Try:

select
    topics.count,
    users.id,
    users.firstname,
    users.lastname,
    users.joined
from
    users left join
    (
        select
            topics.memberid,
            count(*) count
        from
            topics
        group by
            topics.memberid
    ) topics on users.id = topics.memberid

This should gather the count of topics that you're after in a sub query and then left join those counts against all users.

---

Your query is somewhat contradictory. LEFT JOIN will return all rows from the left table, even if there are no matches in the right table. HOWEVER in the case of your query you are grouping your records by Topics.MemberID. For users with no post their will be no record shown!! Try the following query:

SELECT 
COUNT(Topics.MemberID) AS NumberOfTopics, Users.id, 
Users.FirstName, Users.LastName, Users.Joined 
FROM Users LEFT JOIN Topics ON Users.id=Topics.MemberID 
GROUP BY Users.id

---

Your query seems incorrect.
I'd use an inner join

SELECT COUNT(*) AS NumberOfTopics
  , Users.id
  , Users.FirstName
  , Users.LastName
  , Users.Joined 
FROM Users 
INNER JOIN Topics ON Users.id=Topics.MemberID 
GROUP BY Topics.MemberID