MySQL：如何返回表中的所有行，并从另一个表中计算具有匹配ID的行数

Question

I'm a front-end developer by trade, please have mercy on my soul for the horrific PHP and SQL, I'm here to learn!

So, I have a couple of tables, let's call them "categories", and "posts"

My "category" table has these fields:

• categoryID
• categoryName
• categoryDisplayName

My "posts" table has these fields:

• postID
• postTitle
• postCategoryID
• otherNoneImportantFields

I can output all my Categories very simply:

$getCategories = mysql_query("SELECT categoryID, name, displayName 
                              FROM jobCategories 
                              ORDER BY categoryID ASC");
$numberOfCategories = mysql_num_rows($getCategories);

Then I can do a while loop and output all the categories:

if ($numberOfCategories > 0) {
    while($row = mysql_fetch_array($getCategories)) {
        $categoryID = $row['categoryID'];
        $name = $row['name'];
        $displayName = $row['displayName'];

        echo "<li>" . $displayName . "</li>";
        // I'm using the other variables to create a link
    }
}

Now, to the question: I want a variable in the while loop that is a count of all the posts that has that categoryID. I'm unsure if I can do a subquery, or if I have to do a join to get that variable to output.

As a secondary question, is the PHP in question sane, or have a missed a much easier/cleaner way of doing what I'm doing?

Thanks in advance :)

Answer 1

This will return your jobCategories table with an extra column postsCount equal to the number of posts matching the row's categoryID.

SELECT categoryID, categoryName, categoryDisplayName, IFNULL(postsCounts.cnt, 0) AS postsCount
FROM jobCategories
LEFT JOIN (
  SELECT postCategoryID, count(*) as cnt
  FROM posts
  GROUP BY postCategoryID
) postCounts 
ON postCounts.postCategoryID = jobCategories.categoryID