Threads sort incorrectly

[MySQL_Narb]

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.

[requinix]

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?

[MySQL_Narb]

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.

[requinix]

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

[MySQL_Narb]

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

[requinix]

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().