3

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 :)

8
  • Please, stop using mysql_* functions. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and consider using PDO, it's not as hard as you think. Commented Jun 1, 2015 at 20:30
  • @Jay: While one shouldn't be writing new code using mysql_ functions, why do you assume that the OP has an option to use something else? And why is using prepared statements and PDO the only option? Commented Jun 1, 2015 at 21:03
  • I don't assume anything and I ask they 'consider' using PDO @symcbean. ¯\_(ツ)_/¯ Prepared statements should be used regardless of the new API you choose to use for your database operations as they help grandly reduce the risk for SQL Injection. Commented Jun 1, 2015 at 21:06
  • Apologies for not even knowing about PDO. I shall do my best to look into it and see if I can rewrite the simple things at first. Thanks for the heads up. Commented Jun 1, 2015 at 21:15
  • 4
    @JayBlanchard You'll be glad to know that I am now using PDO - your site was a great help to getting started. Now there isn't a single mysql_* function in sight. Commented Jun 10, 2015 at 9:59

2 Answers 2

0

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
Sign up to request clarification or add additional context in comments.

2 Comments

Why should the OP "try this"? Please add an explanation of what you did and why you did it that way, not only for the OP but for future visitors to SO.
Just wanted to let you know that this helped me out a lot. I managed to get the count working from this. Thanks :)
0

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";
 } 

1 Comment

Thank you very much for this. I have a feeling this should work (I'm doing more of a proof of concept as opposed to a polished build) - however, I'm getting the following error: Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given... I've seen this before, I'll do some digging and see if I can find a fix. Thanks again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.