MySQL_Narb Posted June 23, 2012 Share Posted June 23, 2012 All my threads on my forum are sorted by the fields: sticky, lastbump, lastpost Here is the part of my query: ORDER BY `sticky` DESC, `lastbump` DESC,`lastpost`DESC For some reason, even if the lastpost DATE field is newer than the lastbump (PHP's time() function) field, the thread won't be moved to the top of the forum. It seems to only sort by lastbump. E.G: I make a new reply to a thread that's near the bottom of the forums. It updates the lastpost field with the new date. But, it doesn't go to the top of the forums. It stays where it is in the list, and only moves to the top of the forums when the lastbump field is updated. Quote Link to comment https://forums.phpfreaks.com/topic/264673-threads-sort-incorrectly/ Share on other sites More sharing options...
requinix Posted June 23, 2012 Share Posted June 23, 2012 Well yeah: because you're sorting by the lastbump before the lastpost. What's the difference between a bump and a post? Are those two different things? Can someone bump a thread without posting? Quote Link to comment https://forums.phpfreaks.com/topic/264673-threads-sort-incorrectly/#findComment-1356488 Share on other sites More sharing options...
MySQL_Narb Posted June 24, 2012 Author Share Posted June 24, 2012 Well yeah: because you're sorting by the lastbump before the lastpost. What's the difference between a bump and a post? Are those two different things? Can someone bump a thread without posting? I tried switching the positions, but it still didn't work properly. And yes. There's a little button for post owners to bump their threads without having to actually reply. Quote Link to comment https://forums.phpfreaks.com/topic/264673-threads-sort-incorrectly/#findComment-1356517 Share on other sites More sharing options...
requinix Posted June 24, 2012 Share Posted June 24, 2012 I tried switching the positions, but it still didn't work properly. And then it would sort by bump first and most recent post second. It'd be nice if you had one field with the most recent action, be that bump or post, but that might not be a good option. So SELECT ..., IF(lastbump > lastpost, lastbump, lastpost) AS lastaction ... ORDER BY sticky DESC, lastaction DESC Quote Link to comment https://forums.phpfreaks.com/topic/264673-threads-sort-incorrectly/#findComment-1356520 Share on other sites More sharing options...
MySQL_Narb Posted June 24, 2012 Author Share Posted June 24, 2012 One of them is the DATE field in MySQL, and the other is a INT field. Not sure they can work together. Is there a way you can setup the query to check like so: IF lastbump > 0 then ORDER BY lastbump DESC or else it'll ORDER BY lastpost Quote Link to comment https://forums.phpfreaks.com/topic/264673-threads-sort-incorrectly/#findComment-1356565 Share on other sites More sharing options...
requinix Posted June 24, 2012 Share Posted June 24, 2012 No. If you don't feel like changing your table structure to consistently use one type of date field or another, convert between the two using UNIX_TIMESTAMP() or FROM_UNIXTIME(). Quote Link to comment https://forums.phpfreaks.com/topic/264673-threads-sort-incorrectly/#findComment-1356663 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.