MySQL: How to return all rows in a table and count the amount of rows with matching ID from another table

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

Answer 2

I can output all my Categories very simply

Even when you have a million rows in your database?

Yes, you can do a sub-query or a join. The important thing is not to generate a second SQL script inside the loop and keep executing it (because that will be very innefficient).

Sub-query:

SELECT categoryID 
,   name
,   displayName
,   (SELECT COUNT(*)
     FROM posts
     WHERE posts.postCategoryID=jobCategories.categoryID
    ) AS countPosts 
FROM jobCategories 
ORDER BY categoryID ASC;

Join:

SELECT categoryID 
,   name
,   displayName
,   SUM(IF(jobCategories.categoryID IS NULL, 0, 1)) AS countPosts
FROM jobCategories 
LEFT JOIN posts
ON posts.postCategoryID=jobCategories.categoryID
GROUP BY categoryID 
,   name
,   displayName
ORDER BY categoryID ASC;

is the PHP in question sane

In addition to the problem of the number of records, you won't know the $numberOfCategories without running a query first - which is unnecessary when the if () {....} has no impact at all on the behaviour of the script. Even of there is an else {} caluse, it is probably going to be a lot more efficient to test the case after iterating through the loop:

 $count=0;
 while($row = mysql_fetch_array($getCategories) && ++$count<100) {
    ...
 }
 if (!$count) {
    print "No data found";
 }