PHP MySQL - Counting Number of Rows from a select statement

Question

I am attempting to create a discussion board, and am trying to display the number of topics and number of total posts in each forum. I believe the issue lies somewhere in my WHERE clause, as that was causing me issues before on a previous issue.

I know that rowCount() shouldn't be relied on, and in my case doesn't work anyway, mainly due to the fact that it doesn't work with SELECT statements.

I have looked into both SELECT count(*) statements as well as doing $row = $stmt->fetchAll(); $numrows = count($row) However, I didn't get either of them to work.

<?php
        /* CATEGORIES */
            /* SELECTS ALL OF THE CATEGORIES OF THE BOARD */

            $query = "SELECT * FROM bkg_categories";

            try {
                $stmt = $db->prepare($query);
                $result = $stmt->execute();
            } catch(PDOException $e) {
                $error[] = "An error has occured. Please try again later.";
            }
            $categories = $stmt->fetchAll();

        /* FORUMS */
            /* SELECT ALL OF THE FORUMS IN EACH CATEGORY FOR THE BOARD */
            foreach($categories as $category) {
                $catid = $category['category_id'];

                $query = "SELECT * FROM bkg_forums WHERE category_id = :catid";
                $query_params = array(':catid' => $catid);

                try {
                    $stmt = $db->prepare($query);
                    $result = $stmt->execute($query_params);
                } catch(PDOException $e) {
                    $error[] = "An error has occured. Please try again later.";
                }
                $forums[$catid] = $stmt->fetchAll();

                foreach($forums[$category['category_id']] as $forum) {
                                $query = "SELECT * FROM bkg_topics where forum_id = :forumid";
                                $query_params = array(':forumid' => $forum['forum_id']);

                                try {
                                    $stmt = $db->prepare($query);
                                    $result = $stmt->execute($query_params);
                                } catch(PDOException $e) {
                                    $error[] = "An error has occured. Please try again later.";
                                }
                                $rows3['forumid'] = $stmt->fetchAll();
                                $rowCount = count($rows3['forumid']);
                                echo $rowCount;
                                var_dump($rowCount);
                            }



            }
        /* END QUERIES */
    ?>

These are all of my select queries that I am using. In that last foreach loop in my PHP select queries, you can see what I was playing around with, the var_dump() and the echo works, when it's not inside any foreach loop except for the inital parent one, but once I move it down into my table, it doesn't seem to work.

<?php foreach($categories as $category): ?>
                <table class="forumtable">
                    <tr>
                        <td colspan="2"><strong><?php echo $category['category_name']; ?></strong></td>
                        <td><strong>Lastest Post</strong></td>
                        <td><strong>Topics</strong></td>
                        <td><strong>Posts</strong></td>
                    </tr>
                <?php foreach($forums[$category['category_id']] as $forum): ?>
                    <tr>
                        <td width="5%" class="forum"></td>
                        <td class="forum no-padding">
                            <a href="viewforum.php?f=<?php echo $forum['forum_id']; ?>"><?php echo $forum['forum_name']; ?></a>
                            <div class="description"><?php echo $forum['forum_desc']; ?></div>
                        </td>
                        <td width="15%"  class="forum content">    
                            <?php if($forum['forum_last_post_topic_id'] == 0): ?>
                                <div>No posts...</div>
                            <?php else: ?>
                                <?php if($forum['forum_last_post_id'] == 0): ?>
                                    <a href="viewtopic.php?f=<?php echo $forum['forum_id']; ?>&t=<?php echo $forum['forum_last_post_topic_id']; ?>"><?php echo substr($forum['forum_last_post_title'], 0, 10); ?></a>
                                <?php else: ?>
                                    <a href="viewtopic.php?f=<?php echo $forum['forum_id']; ?>&t=<?php echo $forum['forum_last_post_topic_id']; ?>#<?php echo $forum['forum_last_post_id']; ?>"><?php echo substr($forum['forum_last_post_title'], 0, 10); ?></a>
                                <?php endif; ?>
                            <?php endif; ?>
                        </td>
                        <td width="5%"  class="forum content">
                            <?php 
                             echo $forum['forum_topics']; ?></td>
                        <td width="5%"  class="forum content"><?php 
                             echo $forum['forum_posts']; ?></td>
                    </tr> 
                    <?php endforeach; ?>                    
                </table>
            <?php endforeach; ?>

Answer 1

I was able to finally get the select(*) to work.

$db->query('SELECT COUNT(*) FROM users')->fetchColumn(); was what I used to get what I needed to work.