Mysql custom Forum query
Hello, I wrote this query to pull out topic names, the author, number of replies and the lastpost. It worked for a bit then started to not work.
SELECT thread.* , user.username, (COUNT(*)-1) as num_replies, MAX(thread.posting_date ) AS max_date FROM `mb_forum_thread`as thread left join mb_users as user on thread.user_id=user.user_id where thread.forum_id = 3 AND (thread.replyto=0 OR thread.replyto=thread.thread_id) group by thread.thread_id ORDER BY max_date DESC
the threads and topics are in one database, topics identified by replyto field=0 and threads identified by replyto field= thread_id.
the thread table is joined with user table to get the author of a topic.
the above query returns the topics only which is right but it fails on the number of replies for each topic and the last post in a topic.
Firstly, why are you asking for thread.* ?
Secondly, please say what exactly is no longer working. Did you change the DB schema or is the query too slow?
Thirdly, are you doing a group by just because you had a select *?
SELECT U.username, T.?TOPIC?, COUNT(T.thread_id)-1 as num_replies, MAX(T.posting_date ) AS max_date
FROM `mb_forum_thread`as T
left join mb_users as U on T.user_id=U.user_id
where T.forum_id = 3
AND (T.replyto=0 OR T.replyto=T.thread_id)
ORDER BY max_date DESC
What isn't working
Well the num_replies is always 0 and the max_date is wrong as well
I ask for thread.* because I use all the columns in a template
if this helps
CREATE TABLE `mb_forum_thread` (
`thread_id` int(11) NOT NULL auto_increment,
`replyto` int(11) NOT NULL default '0',
`forum_id` int(11) NOT NULL default '0',
`user_id` int(11) NOT NULL default '0',
`subject` varchar(255) NOT NULL default 'Without Subject',
`posting_date` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`thread_id`)