Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006
    Posts
    26

    Unanswered: Problem with query

    I made a forum. All works well but with one exception. If a reply has not been made to a topic, the topic does not appear on the list of topics.

    Here is the query I am using:

    Code:
    SELECT t.*, r.*, DATE_FORMAT(t.topic_last_reply, '%c/%e/%Y %l:%i %p') AS convdate FROM topics t JOIN replies r ON (t.topic_id = r.reply_topic) WHERE t.topic_forum = ".$_GET['forum_id']." GROUP BY t.topic_subject ORDER BY t.topic_last_reply DESC
    When a reply is made, it increments the value in topic_num_replies +1. So what I did is add a HAVING clause:

    Code:
    SELECT t.*, r.*, DATE_FORMAT(t.topic_last_reply, '%c/%e/%Y %l:%i %p') AS convdate FROM topics t JOIN replies r ON (t.topic_id = r.reply_topic) WHERE t.topic_forum = ".$_GET['forum_id']." GROUP BY t.topic_subject HAVING t.topic_num_replies >= 0 ORDER BY t.topic_last_reply DESC
    I get the same result. As soon as I make a reply to the topic it appears on the list.

    Thanks for your advice!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You want to change your JOIN from an INNER to a LEFT on the replies table.
    The reason the second query didn;t erturn aresult is because when there are no replies then the count is NULL, not zero
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2006
    Posts
    26
    Thanks for your reply.

    I made it a LEFT join but the results are still the same. I am running this in MySQL directly to make sure no PHP is getting in the way.

    Thanks for your help.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    post the SQL when using a left join please
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2006
    Posts
    26
    Hey man, thanks but I got it working. I don't know what I did. Maybe it was just getting a new browser open.

Posting Permissions

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