datetime sorting

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
User avatar
pleigh
Forum Contributor
Posts: 445
Joined: Wed Jan 19, 2005 4:26 am

datetime sorting

Post 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 :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
User avatar
pleigh
Forum Contributor
Posts: 445
Joined: Wed Jan 19, 2005 4:26 am

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

so then all posts which don't have comments will always show first.. forever. you sure you want that?
User avatar
pleigh
Forum Contributor
Posts: 445
Joined: Wed Jan 19, 2005 4:26 am

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
pleigh
Forum Contributor
Posts: 445
Joined: Wed Jan 19, 2005 4:26 am

Post 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??

:)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
pleigh
Forum Contributor
Posts: 445
Joined: Wed Jan 19, 2005 4:26 am

Post by pleigh »

yes...thanks feyd...i think this problem is fixed.... :wink:
Post Reply