1

http://www.vidyasocks.com/requests.php

I want it so when I post in a thread it will bump that thread to the top.

This is what I currently have for the page"SELECT * from forum ORDER BY id DESC LIMIT 12";

The database has thread with an id(pk) and replies with a id(pk) and a thread_id(fk)

What do I do? Some kind of inner join I'd assume?

Web Owl
  • 567
  • 2
  • 15
  • 29
  • `:)` @ scrolltext. That should be `you're too slow` (apostrophe in contracted you-are, too instead of to). – halfer May 05 '12 at 05:40

1 Answers1

2

Do you have a date/timestamp for when threads and replies are inserted? You can't just order based on reply ID because there are threads without any reply and it isn't possible to correlate thread_id order with the reply id order.

If I have the right idea for your table structure you'd be doing something like this given some timestamps:

 SELECT DISTINCT forum.* FROM forum 
      LEFT JOIN replies on
          forum.id = replies.thread_id
  ORDER BY coalesce(replies.reply_timestamp, forum.thread_timestamp) DESC
    LIMIT 12

The coalesce would return replies.reply_timestamp if a reply exists otherwise the forum.thread_timestamp would be used.

edit: added DISTINCT based on Ami's comment

nvuono
  • 3,323
  • 26
  • 27
  • With nulls coming last that would put all threads without a reply (null reply_timestamp) at the bottom regardless of their thread_timestamp value. The group of threads without a reply would then be sorted by their thread_timestamp but they still won't be ordered within the group of reply_timestamp rows. – nvuono May 05 '12 at 05:41
  • @nvuono, you're absolutely right. I withdraw my suggestion. :) It sucks that we have to live with the coalesce. – Ami May 05 '12 at 05:48