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!
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.
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:
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
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?
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