Jump to content


Photo

Help Please!


  • Please log in to reply
4 replies to this topic

#1 Aborted_Fetus

Aborted_Fetus
  • New Members
  • Pip
  • Newbie
  • 3 posts
  • LocationMinneapolis, MN

Posted 23 October 2003 - 02:07 AM

I am trying to create my own forum system and am trying to replicate the way phpBB sorts posts with recent replies to the top of the list. I have all posts and replies in one table and am trying not to add a last_reply field to the table.

here goes,

I have one table lets call it T1

for this purpose we can say T1 has 3 fields:

message_ID - Auto_Incremented Primary Key (each one is unique)

parent_ID - INT (where parent_ID = 0 if not a reply and parent_id=message_id of post relpied to)

lastdate - TIMESTAMP (date posted)

Here is what I want to do with this:
First I want to select all messages with such that parent_id=0
then I want this sorted in the order of which has the most recent reply so it would have to sort through the replies of each and pick out the most recent date and sort the list of parent_id=0.

this just has my head spinning

I am using mysql 3.22
Is there a way for me to do this in just sql? if not php? am I just going to have to do something different?

I would be soooo grateful for any help!
Not just a sad sack of cells anymore...

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 23 October 2003 - 06:36 PM

SELECT * FROM T1 WHERE parent_id = 0 ORDER BY lastdate DESC

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 Aborted_Fetus

Aborted_Fetus
  • New Members
  • Pip
  • Newbie
  • 3 posts
  • LocationMinneapolis, MN

Posted 23 October 2003 - 10:36 PM

maybe I made it confusing.

lastdate is supposed to called postdate actually. There is no lastdate column.

each row contains a message_id a parent_id and postdate

first I want to select all with parent_id = 0 that is no problem

to get each message which is not a reply i do this

SELECT* FROM T1 WHERE parent_id=0 ORDER BY postdate)

to get the replies for that message I do this
SELECT * FROM T1 WHERE parent_id=\'id of message getting replies of\' ORDER BY postdate

Now when I display the messages with that first query I want those results to be sorted by the dates of each in the second query

get it?[/code]
Not just a sad sack of cells anymore...

#4 nabeel21

nabeel21
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 24 October 2003 - 12:29 AM

if i understand this right...

I think you should update the \'postdate\' field everytime a person posts a reply. When u change the parent_id also update the \'postdate\'. Then you can sort the data with the replied ones at the top.


--hope this makes sense :)

#5 Aborted_Fetus

Aborted_Fetus
  • New Members
  • Pip
  • Newbie
  • 3 posts
  • LocationMinneapolis, MN

Posted 24 October 2003 - 02:18 AM

I know that I could do that but I was hoping for another way so I wouldn\'t need to modify anything but the sql query. Oh well, I guess I am just going to have to change a few things around.

thanks anyway though :)
Not just a sad sack of cells anymore...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users