MySQL Order By

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
watson516
Forum Contributor
Posts: 198
Joined: Mon Mar 20, 2006 9:19 pm
Location: Hamilton, Ontario

MySQL Order By

Post 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";
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: MySQL Order By

Post 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
watson516
Forum Contributor
Posts: 198
Joined: Mon Mar 20, 2006 9:19 pm
Location: Hamilton, Ontario

Re: MySQL Order By

Post 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?
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: MySQL Order By

Post 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.
Post Reply