Page 1 of 1
MySQL Order By
Posted: Fri May 08, 2009 10:31 am
by watson516
I am trying to create a small forum and I have run into the problem of sorting the threads by date.
I need to put the new threads and threads with new replies on top of the list (as with every forum out there) but I can't seem to figure it out. Is it possible to ORDER BY multiple columns at the same time instead of ordering by 1 column and then the second column after that?
Here is my current mysql statement that doesn't fully work.
Code: Select all
$q="SELECT * FROM forum_threads LEFT JOIN forum_reply ON reply_tid = threads_id WHERE threads_fid = $fid ORDER BY reply_date DESC, threads_date DESC";
Re: MySQL Order By
Posted: Fri May 08, 2009 10:37 am
by jayshields
What is considered to be a new reply? A reply since your last visit?
I imagine logic would be to show all threads with activity since you last visited first, ordered by last activity date. Then show all the other threads, ordered by last activity date.
You could do something like that by using an IF and ordering on the result of that. Something like:
Code: Select all
SELECT *, IF(`last_active_date` > `last_visit_date`, 1, 0) AS `active_since_last_visit` FROM `forum_threads` ORDER BY `active_since_last_visit` DESC, `activity_date` DESC
Re: MySQL Order By
Posted: Fri May 08, 2009 10:59 am
by watson516
Wouldn't that (last activity date) be sort of irrelevant for the sorting because it will always sort the threads by the most recent activity no matter when the user was last active? Maybe I'm missing something?
Re: MySQL Order By
Posted: Fri May 08, 2009 11:27 am
by jayshields
I don't know what you mean, but maybe you're getting confused because I invented some fields or you don't know how multiple ordering works. Basically the logic (as I see it) would be to fetch a resultset which basically shows all the threads with a parition in the middle. The top-most threads are the ones with activity which the user hasn't had chance to see yet, ordered by the date of acitivty. The bottom-most threads are ones with activity from before the users' last visit, also order by date of activity.
There's a slight error in my example query - the order by should be
Code: Select all
ORDER BY `active_since_last_visit` DESC, `last_active_date` DESC
I didn't mention that I assume you've got the users' last login date stored somewhere - that data is referred to with `last_visit_date` in my query.