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?