CODEWITHSUNDEEP
phpmysql
user3096443
user3096443

Reputation: 180

SQL query to select all forum threads but order them by the latest active thread

I'm currently using this code to order the forum threads within my forum according to the date they were created.

$e = mysqli_real_escape_string($_GET["forum"]);
$q = mysqli_query($con,
     "SELECT * FROM threads WHERE forum = '$e' ORDER BY date DESC LIMIT $offset,$rowsPerPage");

However, this order's the threads by their creation date rather than their activity.
How can I order the threads by their activity?

I'm thinking of something along the lines of
SELECT * FROM threads WHERE forum = '$e' ORDER BY (replies in this thread).date DESC LIMIT 5

Is this possible or not?

EDIT:
table thread holds the inital thread data
table replies holds the replies

Upvotes: 0

Views: 424

Answers (2)

jko
jko

Reputation: 2098

a subselect-join will do the trick:

select t.* , lp.latestpost 
from threads as t 
inner join ( 
  select p.thread_id , max(p.date) as latestpost 
  from replies as p 
  group by p.thread_id
) as lp on pl.thread_id = t.id
order by lp.latestpost desc

Upvotes: 0

Thomas Ruiz
Thomas Ruiz

Reputation: 3661

Add a column containing the last time a thread has been replied into your threads table, and then use order by this col. Don't forget to update the column on new reply.

There are ways to do it manually, but it will be far more slower than a dedicated column.

Upvotes: 1

Related Questions