Hey all,
I've run into a new challenge in my PHP/MySQL learning experience that I was hoping someone could offer some input on. I'll do my best to explain the circumstances.
I'm working on a Forum like section of code. It runs on two MySQL tables, FORUM_POSTS and FORUM_REPLIES. In the main Forum section, it selects all of the FORUM_POSTS entries and displays them with a while statement, ordered by ID, newest first.
I'd like to be able to order them by order of the latest reply, but I am unsure how to do this, as the replies are stored in the FORUM_REPLIES table.
Does anyone have any suggestions on the most reasonable, or most effective way to go about accomplishing that? I'm sure I could indeed come up with a fix for it, but I'm unsure if it would be the best way as I'm new to this.
One way I considered was adding an additional ID2 column to the FORUM_POSTS table and incrementing it each time a reply is made in reference to it, then doing an ORDER by that ID number instead.
Any tips/suggestions would be cool. Thanks!
-Sysin the FORUM_POSTS table, create a new column using the ALTER TABLE:
ALTER TABLE FORUM_POSTS ADD COLUMN Last_Reply TIMESTAMP(14);
This will allow you to put in the time of the latest reply by using:
UPDATE TABLE FORUM_POSTS SET Last_Reply = NOW() WHERE ID=****;
where **** is the id of the thread being replied to...
everytime you insert a reply into the reply table, just update the post table with the new timestamp.
then when you want to sort the threads by the date of last reply:
SELECT * FROM FORUM_POSTS ORDER BY Last_Reply;
I can't remember if you want ASC or DESC
(I am no use at remembering labels for directions).Horus,
That seems like a very logical solution as well. I will have to try that out. I'm still wondering what the best bang for the buck methods are though, as far as performance hit, etc.
-SysWell, the advantage to that solution is...it's easy for someone to follow in the future, if they have a need to go through your code, and figure out what you were doing. How are the tables formatted, and what do you use to link the replies to the original post?Originally posted by sysera
I'm working on a Forum like section of code. It runs on two MySQL tables, FORUM_POSTS and FORUM_REPLIES. In the main Forum section, it selects all of the FORUM_POSTS entries and displays them with a while statement, ordered by ID, newest first.
I'd like to be able to order them by order of the latest reply, but I am unsure how to do this, as the replies are stored in the FORUM_REPLIES table.
-Sys
ok, so just query the FORUM_REPLIES instead of FORUM_POSTS
HK,
ASC = small to big: old to new
DESC = big to small:new to old
Originally Posted by Scoutt
ASC = small to big: old to new
DESC = big to small:new to old
It's no use trying to teach me - i have a problem with abstracting labels to directions...
it's weird - i can find my way through some wilderness with minimal map references and no compass, but try and get me to figure my "left" from my "right"...
I've run into a new challenge in my PHP/MySQL learning experience that I was hoping someone could offer some input on. I'll do my best to explain the circumstances.
I'm working on a Forum like section of code. It runs on two MySQL tables, FORUM_POSTS and FORUM_REPLIES. In the main Forum section, it selects all of the FORUM_POSTS entries and displays them with a while statement, ordered by ID, newest first.
I'd like to be able to order them by order of the latest reply, but I am unsure how to do this, as the replies are stored in the FORUM_REPLIES table.
Does anyone have any suggestions on the most reasonable, or most effective way to go about accomplishing that? I'm sure I could indeed come up with a fix for it, but I'm unsure if it would be the best way as I'm new to this.
One way I considered was adding an additional ID2 column to the FORUM_POSTS table and incrementing it each time a reply is made in reference to it, then doing an ORDER by that ID number instead.
Any tips/suggestions would be cool. Thanks!
-Sysin the FORUM_POSTS table, create a new column using the ALTER TABLE:
ALTER TABLE FORUM_POSTS ADD COLUMN Last_Reply TIMESTAMP(14);
This will allow you to put in the time of the latest reply by using:
UPDATE TABLE FORUM_POSTS SET Last_Reply = NOW() WHERE ID=****;
where **** is the id of the thread being replied to...
everytime you insert a reply into the reply table, just update the post table with the new timestamp.
then when you want to sort the threads by the date of last reply:
SELECT * FROM FORUM_POSTS ORDER BY Last_Reply;
I can't remember if you want ASC or DESC
(I am no use at remembering labels for directions).Horus,
That seems like a very logical solution as well. I will have to try that out. I'm still wondering what the best bang for the buck methods are though, as far as performance hit, etc.
-SysWell, the advantage to that solution is...it's easy for someone to follow in the future, if they have a need to go through your code, and figure out what you were doing. How are the tables formatted, and what do you use to link the replies to the original post?Originally posted by sysera
I'm working on a Forum like section of code. It runs on two MySQL tables, FORUM_POSTS and FORUM_REPLIES. In the main Forum section, it selects all of the FORUM_POSTS entries and displays them with a while statement, ordered by ID, newest first.
I'd like to be able to order them by order of the latest reply, but I am unsure how to do this, as the replies are stored in the FORUM_REPLIES table.
-Sys
ok, so just query the FORUM_REPLIES instead of FORUM_POSTS
HK,
ASC = small to big: old to new
DESC = big to small:new to old
Originally Posted by ScouttASC = small to big: old to new
DESC = big to small:new to old
It's no use trying to teach me - i have a problem with abstracting labels to directions...
it's weird - i can find my way through some wilderness with minimal map references and no compass, but try and get me to figure my "left" from my "right"...