SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Nelson
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.
    [query]
    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
    [/query]

    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.

    Any insight?

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 *?

    Try this:

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

  3. #3
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Nelson
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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',
    `message` text,
    `posting_date` datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY (`thread_id`)
    )


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •