MySQL: how to return all rows in a table and count the number of rows with matching ID from another table
I am a leading developer by profession, please have mercy on my soul for the terrible PHP and SQL, I am here to find out!
So I have a couple of tables, call them "categories" and "posts"
My "category" table has the following fields:
- CategoryId
- CategoryName
- categoryDisplayName
My posts table has the following fields:
- messages given
- postTitle
- postCategoryID
- otherNoneImportantFields
I can display 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 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 the variable in the while loop to be the count of all posts that have this category ID. I'm not sure if I can make a subquery, or if I need to make a join to get this variable for output.
As a side question, is PHP sane, or have you missed a much easier / cleaner way of doing what I am doing?
Thank you in advance:)
source to share
This will return your jobsCategories table with an extra column postsCount
equal to the number of posts matching the categoryID row.
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
source to share
I can display all my categories very easily
Even if you have a million rows in your database?
Yes, you can do subquery or join. It is important not to generate the second SQL script loop inside the loop and keep executing it (because that would be very inefficient).
Sub-request:
SELECT categoryID
, name
, displayName
, (SELECT COUNT(*)
FROM posts
WHERE posts.postCategoryID=jobCategories.categoryID
) AS countPosts
FROM jobCategories
ORDER BY categoryID ASC;
Check in:
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 question in question,
In addition to the post count issue, you won't know $ numberOfCategories without running the query first - which is optional when if () {....} has no effect on behavior from within the script. Even with an else {} caluse, it will probably be much 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";
}
source to share