logic behind a reply function

[omoutop]

hello all
i need some guidance/ideas on how to build a reply function.

For simplicity we consider a forum thread or a comment blog script with notes/comments.
Lets assume the someone sends a message (title + actual text).
That is saved into a database.
The owner/recipient replies with a text, to the original message.
This can go on for a countless number of times, for a countless conversations of course.

Lets assume that a database table holds only the minimum data (msg_id, user_id, msg_title, msg_text, post_date)


Whats the best approach into building such a script?
Whats the logic behind it?
How can i store data in order to know what message is a reply? And how to build the whole conversation?

[rocknbil]

There really is no "best way," there are hundreds of ways. If it's an existing system, build it on what's been started, otherwise, some ideas. I'll start with this one:

How can i store data in order to know what message is a reply?

Fairly easy, and gets important when you go to move or delete stuff.

id|thread_id|post_id|reply_to|userid|content|post_time

So if it's a reply you might have

1234|4567|4566346|4568|56547|Bleah|2010-026-10 12:37:34

A reply just to a thread, or the original post:

1234|4567|4566346|0|56547|Bleah too|2010-026-10 12:18:34

The ordering by date would take care of where they fall, but you would use the reply_to value to determine any visual indenting, etc.

select * from posts where thread_id=4567 order by post_time asc;

Then comes pagination, user preferences (examples, thread view, sort in direction, make user "annoying" invisible), admin preferences/banning, templating, avatars, signatures . . . . you sure you want to tackle this? :-)

[omoutop]

you sure you want to tackle this? :-)

yeah - the data itself are no problem - i am not building a forum from scratch - it's just a simple note/comment application and i want to try some basic reply functionality.

So, following your example, if i order by reply_id AND date i would be ok, no?
First come the reply_id = 0 (which is the original post), followed by a bunch of posts on different dates (which act as replies to the id=0 post).

Have i understood you correctly?

[rocknbil]

No, if you do

select * from posts where thread_id=4567 order by reply_id asc, post_time asc;

**ALL** the replies to this thread would float to the top, and would actually remove their "connection" from the post. post_time should suffice, constructs some test data to see.

Let's take a nearly impossible scenario, all post_times the same. So if you want to really be sure, do this.

select * from posts where thread_id=4567 order by post_time asc, id asc, reply_id asc;

The auto increment id field would insure they stay in order, and truthfully, reply_id would have no effect as the replies would also have an id and that ordering would take precedence . . . . not really even needed, see?

[omoutop]

I see.. thanks for the guidelines. Now lets see how tests will go

[lostdreamer]

Could be just me, but I think not...

select * from posts where thread_id=4567 order by post_time asc

imagine the following case chronologicly:
1) User A posts a new topic
2) User B posts a reply message to A
3) User C posts reply to B
4) User D posts reply to A

Sorting it by date would be the wrong way here....
Since you would like to see all the replies to the first post on top (right beneath the TS)

select * from posts where thread_id=4567 order by reply_id, post_time asc

Would indeed give the expected result.

[omoutop]

Lostdreamer, as i have said.. this is not for forum.
Its a simple communication between 2 persons only

so person A send to B
B replies to A
A replies to B and so on untill conversation is closed

In my case i think its safe to sort by post_date (i use timestamps), yet i recognize the logic behind ordering by the autoinc field (and i use that way now).

[rocknbil]

Yes, but he/she is correct. :-) if someone comes in later and replies to an earlier post, by date only would be out of order, all I can say is "#*$! was I thinking." I guess I wasn't, sorry.