How do I count the number of replies in a board of my custom forum?

Question

I am developing a custom forum with PHP and MySQL. I have three primary tables in this scenario: boards, threads, and messages. I would like to count the number of messages in a board. The threads table has a column called "first_msg_id" that is a reference to the first message of that thread. My query shouldn't count this message.

CREATE TABLE IF NOT EXISTS `forum_messages` (
  `message_id` int(15) NOT NULL AUTO_INCREMENT,
  `thread_id` int(15) NOT NULL,
  `author_id` int(15) NOT NULL,
  `modifier_id` int(15) DEFAULT NULL,
  `content` text NOT NULL,
  `date_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_modified` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`message_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `forum_threads` (
  `thread_id` int(15) NOT NULL AUTO_INCREMENT,
  `board_id` int(15) NOT NULL,
  `first_msg_id` int(15) NOT NULL,
  `last_msg_id` int(15) NOT NULL,
  `author_id` int(15) NOT NULL,
  `updater_id` int(15) NOT NULL,
  `title` text NOT NULL,
  `date_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `views` int(15) NOT NULL,
  `status` tinyint(1) NOT NULL,
  `type` tinyint(1) NOT NULL COMMENT '0 normal, 1 sticky, 2 global.',
  PRIMARY KEY (`thread_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `forum_boards` (
  `board_id` int(15) NOT NULL AUTO_INCREMENT,
  `parent_id` int(15) NOT NULL,
  `category_id` int(15) NOT NULL,
  `last_msg_id` int(15) NOT NULL,
  `position` tinyint(1) NOT NULL,
  `title` text NOT NULL,
  `description` text NOT NULL,
  `status` tinyint(1) NOT NULL,
  `thread_count` int(15) NOT NULL,
  `reply_count` int(15) NOT NULL,
  PRIMARY KEY (`board_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

This is the query I have:

$query_board_replies = "
                    SELECT
                        m.message_id, m.thread_id,
                        t.thread_id, t.first_msg_id, t.board_id
                    FROM forum_messages AS m
                    LEFT JOIN forum_threads AS t ON t.first_msg_id != m.message_id
                    WHERE t.board_id = ".$board_id."
                    ORDER BY m.message_id";

It doesn't return any errors, but it is giving me a completely incorrect count. There are only two actual replies, but it is returning a count of 18 in a particular board.

Any ideas?

Geoffrey Geoffrey · Accepted Answer · 2013-07-15T17:35:44

This should do it.

$query = "
  SELECT
    COUNT(*)
  FROM
    forum_messages A,
    forum_threads  B
  WHERE
    A.thread_id   = B.thread_id    AND
    A.message_id != B.first_msg_id AND
    B.board_id    = " . mysqli_real_escape_string($dbc, $board_id) . "
";
$rs = mysqli_query($dbc, $query);
list($count) = mysqli_fetch_array($rs);
echo $count;

How do I count the number of replies in a board of my custom forum?

1 Answers