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:
My "posts" table has these fields:
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 :)
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
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";
}