Page 1 of 1

datetime sorting

Posted: Mon Feb 28, 2005 11:57 pm
by pleigh
hi there

i have a problem with sorting dates, i originally posting threads sorted by its dates DESC, what i want to do is when some replied to the thread, that thread, even if it was way back months, or years ago, it will be displayed on the first page....i think i have to make some logical coding in mysql but can't find a good one on this...hope you can help me.

thanks

pleigh :)

Posted: Tue Mar 01, 2005 12:11 am
by feyd
typically you'll have 2 tables: topics and posts. The topic table contains the topic title, a few other settings, and a topic id. Posts contains the post information, date of the post and topic id link.

Your query joins those two tables together to determine the order in which topics are shown.

Code: Select all

SELECT DISTINCT t.* FROM `topics` t, `posts` p WHERE p.topicid = t.topicid ORDER BY p.post_time DESC

Posted: Tue Mar 01, 2005 12:55 am
by pleigh
thanks for the reply feyd

i have 2 tables, posts and comments, i have date fields in both tables, i want to sort like this: IF comments.date is NULL, sort it DESC ELSE sort posts.date.i want everytime there is a new comment, it is displayed on the first page....is there a way this conditions be done in mysql?

Posted: Tue Mar 01, 2005 12:57 am
by feyd
so then all posts which don't have comments will always show first.. forever. you sure you want that?

Posted: Tue Mar 01, 2005 12:59 am
by pleigh
sorry...got confused...this is i think the right one..

i have 2 tables, posts and comments, i have date fields in both tables, i want to sort like this: IF comments.date is NULL, sort posts.date DESC ELSE sort comments.date.i want everytime there is a new comment, it is displayed on the first page....is there a way this conditions be done in mysql?

Posted: Tue Mar 01, 2005 1:04 am
by feyd
that'd do the same thing as before.

I'd suggest thinking about redesigning how your posts are stored. Note, in this instance posts are the actual posts.. not a thread.. I'd suggest storing all posts in a table and all thread information (which is partly referenced by those posts) in a seperate table. This would centralize where the date's are stored making a query of this nature easier to deal with Among other issues.

Posted: Tue Mar 01, 2005 1:11 am
by pleigh
ah ok....thanks....how bout this....if i have a posts table with date field, i want to monitor the original date of the message, then if that message is updated, of course the date will be changed....can i instead create a new date field in my posts table, for example name it update, so i can keep track of the date of the update and sort my table using update instead??

:)

Posted: Tue Mar 01, 2005 1:14 am
by feyd
sure... when initially inserted set both date's to the same value. This way you only have to check one field to know the last change to it.. simplifies ordering.

Posted: Tue Mar 01, 2005 1:18 am
by pleigh
yes...thanks feyd...i think this problem is fixed.... :wink: