Databases
StackOverflow2013
Postsdbo
POHow do I use MySQL Join to sort forum threads by their last reply?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I use MySQL Join to sort forum threads by their last reply?
    primarykey
    data
    text
    <p>I'm having a bit of trouble with writing a query for this. I think I have the basic logic down, although I might not. What I want to do is get all the threads based on the board that the user is viewing, and then sort those threads based on the time of their last reply. The query doesn't return any error, it only gets the most recently updated thread.</p> <p>Here's my query:</p> <pre> $query = " SELECT t.child_id, t.thread_id, m.thread_id, m.message_id, m.date_posted FROM forum_threads AS t LEFT JOIN forum_messages AS m ON t.thread_id = m.thread_id WHERE t.child_id = ".$board_id." ORDER BY m.date_posted DESC LIMIT ".$starting.", ".$this->user['results_per_page']; </pre> <p>This is the query that is being returned:</p> <pre> SELECT t.child_id, t.thread_id, m.thread_id, m.message_id, m.date_posted FROM forum_threads AS t LEFT JOIN forum_messages AS m ON t.thread_id = m.thread_id WHERE t.child_id = 2 ORDER BY m.date_posted DESC LIMIT 0, 15 </pre> <p><img src="https://i.stack.imgur.com/mcO9I.gif" alt="enter image description here"></p> <p><img src="https://i.stack.imgur.com/7BaCL.gif" alt="enter image description here"></p> <h1>Update</h1> <p>I have attempted to go with the idea suggested by ethrbunny, although I am completely lost with what is causing it to return an error.</p> <pre> $query = " SELECT t.board_id, t.thread_id FROM forum_threads AS t LEFT JOIN ( SELECT m.thread_id, m.message_id FROM forum_messages AS m WHERE m.thread_id = t.thread_id ORDER BY m.message_id DESC LIMIT 1 ) AS q WHERE t.board_id = ".$board_id." ORDER BY q.date_posted DESC LIMIT ".$starting.", ".$this->user['results_per_page']; </pre> <pre>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE t.board_id = 4 ORDER BY q.date_posted DESC LIMIT' at line 11</pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USD3ad L0cK
    1. USShoeLace1291
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow do I use MySQL Join to sort forum threads by their last reply?
      1. USShoeLace1291
      1. VTBountyStart
    2. VO
      singulars
      1. POHow do I use MySQL Join to sort forum threads by their last reply?
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    3. VO
      singulars
      1. POHow do I use MySQL Join to sort forum threads by their last reply?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAre you just wanting one message with each thread and then sort the whole list by most recent message / thread?
      singulars
      1. POHow do I use MySQL Join to sort forum threads by their last reply?
      1. USethrbunny
    2. COYes, exactly right.
      singulars
      1. POHow do I use MySQL Join to sort forum threads by their last reply?
      1. USShoeLace1291
    3. COIf you're still struggling, consider providing a sqlfiddle.
      singulars
      1. POHow do I use MySQL Join to sort forum threads by their last reply?
      1. USStrawberry
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload