CODEWITHSUNDEEP
phpmysqlsql
Neku
Neku

Reputation: 85

Bump System (Show recent thread with reply on top)

For now I sort all the threads in my forum using this:

SELECT * FROM board ORDER BY id DESC LIMIT 50

This shows the newest thread created on the top, but how would I go about showing the thread that has the most recent reply but also showing a new thread on the top?

Example:

  1. This is a new thread
  2. This is a old thread
  3. This is a old thread

To:

  1. This is a old thread with a new reply
  2. this is a new thread without replies
  3. This is a old thread

This is the reply code if thats any help:

$sql="INSERT INTO reply (id, name, subject, maintext, ip, date, img)

VALUES

('$idid','$name','$subject','$maintext','$encoded','$date','$image_name')";

if (!mysql_query($sql,$con))

  {

  die('Error: ' . mysql_error());

  }

mysql_query("UPDATE board SET replycount = replycount + 1 WHERE id = $idid");

Upvotes: 1

Views: 322

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

If I understand your question, you want to sort by both recent replies and newest threads. I can't give you exact syntax without more information about your tables, but your query might look something like this:

SELECT *
FROM board
ORDER BY GREATEST(replyDateTime, creationDateTime)
LIMIT 50;

This uses the GREATEST function to sort each row by either replyDateTime, or creationDateTime... whichever is more recent.

EDIT:

With your reply information in a separate table, your query could use a left join, and then select the most recent reply for each post in board... and then use GREATEST to select either the creation date of the post, or the most recent reply of the post.

/* You will need to specify all your columns in board instead of using * */
SELECT b.ID, b.CreationTimeStamp, MAX(r.CreationTimestamp)
FROM 
    board b
    LEFT JOIN replies r ON r.BoardID = b.ID
/* Include all your columns in your select from board here in your group by */
GROUP BY b.ID, b.CreationTimeStamp
ORDER BY GREATEST(b.CreationTimestamp, MAX(r.CreationTimestamp))
LIMIT 50;

Upvotes: 2

Related Questions