How do I make replies to comments? (PHP)

Question

I want to create something like reddit where they have comments, then replies to the comment, then reply to the reply.

What type of database structure do they use so:

1. they keep track of all the comments to a posting
2. a reply to a comment
3. a reply to a reply

All I have right are is just a posting and a bunch of comments relating to it like..

POSTING TABLE
posting_id | title | author

COMMENTS TABLE
comment_id | posting_id | comment

REPLIES TABLE
????

How do I relate the comments to the replies? What type of css do they use to give replies that indented space?

EDIT: Thanks for the answers! Now my only question how do I indent the replies? Such as..

you like food
     yes I love italian
        Yes i do like it too
     chinese is best

Answer 1

You can add another column to your comments table specifying parent_comment_id where you populate it with the ID of the comment (or reply) the user is replying to. In the case where the comment is a direct reply to the post (not a reply to a comment) this column would be null.

Answer 2

Add another field to your comments table which "reply_to" or some such, and store the id of the comment which it is in reply to there.

Answer 3

you could make the comments table generic like so :

COMMENTS TABLE
comment_id | posting_type | posting_id | comment

where posting_type is some sort of discriminator, eg a string 'POST' or 'COMMENT', or an integer for more efficiency (1 = POST, 2 = COMMENT, etc).

edit : admittedly this is more complicated but it means you can use the same comment table for comments on anything, not just posts and other comments.

Answer 4

You don't need the replies table. As others already correctly have pointed out, recursion is the way to go with an RDBMS. You could always consider using a nosql style DBMS, to avoid having to deal with recursion.

Answer 5

I would do that by making a cross reference table.

Example:

Table: Posts

Columns: pstkey | userid | postMessage | etc...

pstkey is the key for the post body. userid is the person who created the post. postMessage is the actual post entry.

Table: Comments

Columns: comkey | pstkey | userid | commentMessage | etc...

comkey is the key for the comment made. referenced to the post using the pstkey. userid is the person who made the comment. and then commentMessage is the text body of the actual comment.

Table: xref_postComm

Columns: xrefkey | pstkey | comkey | comkey2 |

Now for the fun part. ALL posts go into post table. ALL comments go into comment table. The relationships are all defined in the Cross Reference Table.

I do all of my programming this way. I was privileged to work with one of the worlds bests database engineers who was retired and he taught me a few tricks.

How to use the Cross Reference table:

xrefkey | pstkey | comkey | comkey2
All that you look for is the population of a given field.

xref (Auto Incremented)
pstkey (Contains the pstkey for the post)
comkey (Contains the comkey for the comment post)
comkey2 (Contains the comkey for the comment post)
        (but only populate comkey2 if comkey already has a value)
and of course you populate comkey2 with the key of the comment.

SEE, no reason for a 3rd tabel!

With this method you can add as many relationships as you want. 
Now or in the future!

comkey2 is your reply to a reply. Where which this single row contains.... the key of the post, the key of the comment, and the key of the reply to the reply comment. All done by population of xref.

EXAMPLE:
PAGES.... Page table

POSTS
 pstkey | pageid | user| Post
-------------------------------------
|   1   |    1   |  45 | Went to the store the....|
|   2   |    2   |  18 | Saw an apple on tv.....

COMMENTS
 comkey | pstkey | user  | Comment
-----------------------------------------------
|   1   |    1   |   9   | Wanted to say thanks...
|   2   |    1   |   7   | Cool I like tha.....
|   3   |    2   |   3   | Great seeing ya....
|   4   |    2   |   6   | Had a great....
|   5   |    2   |   2   | Don't sweat it man...

xref_PostCom
xrefkey | pageid | pstkey | comkey | comkey2 |
----------------------------------------------
|   1   |   1    |   1    |  NULL  |   NULL  | Post1 on Page1
|   2   |   1    |   1    |   1    |   NULL  | Comment1 under Post1
|   3   |   1    |   1    |   2    |   NULL  | Comment2 under Post1
|   4   |   2    |   2    |  NULL  |   NULL  | Post2 on Page2
|   5   |   2    |   2    |   3    |   NULL  | Comment3 under Post2 on Page2
|   6   |   2    |   2    |   4    |   NULL  | Comment4 under Post2 on Page2 (a second Comment)
|   7   |   2    |   2    |   4    |    5    | Explained below....
Comment key 5 is matched with comment key 4....under post2 on Page 2

If you know anything about join, left join, right join, inner/outer join creating SELECT's to get the data arrays using these relationships, your job becomes a whole lot easier.

I believe the engineer's call this basically "the data map" of defined relationships. The trick is now how you access them using these relationships. It seams hard at first, but know what I know it, I refuse to do it any other way.

What happens in the end is you end up writing 1 script that says, ok, go do uhh, everything and come back. You will end up with 1 function call that asks for page 1. It returns with page1, post 1, comment1&2&3 and the replies to the reply in 1 array. echo to output and done.

UPDATE FOR COMMENT I said the same exact thing the first time it was shown to me. As a matter of fact it really was making me mad that the database programmer was forcing me to do it this way. But now I get it. The advantages are so many.

Advantage 1) 1 query can be written to pull it all out in 1 shot.

2) Answers in multiple queries can populate arrays in a structure that when printing the page a loop in a loop can display the page.

3) Upgrading your software that uses it can support any possible design change you can ever think of. Flawless expandability.

The guy who taught it to me was the hired gun who redesigned sears and jcpenny databases. Back when they has 9 books going to the same house because of duplicate records issues.

Cross reference tables prevent a lot of issues in design.

The heart to this theory is, a column can not only contain data but serve as a true or false statement at the same time. That in it's self saves space. Why search 20 tables when you can search one? 1 indexed cross reference table can tell you everything you need to know about the other 20 tables, it contents, what you need, what you don't need, and do you even need to open the other table at all.

IN SHORT: 1 Cross reference containing nothing but INT(2/11) that tells you everything thing you need to know before you ever open another table, not only contains flawless expandability but lighting speed results. Not to mention little possibility of duplicate records. To you and me duplicate records may not be an issue. But to Sears with 4 billion records at $11 a book, mistakes add up.

Answer 6

To show replies inside replies, you'll have to do a recursive call to keep on generating the sub replies.

Something like

function get_comments($comment_id) {
    print '<div class="comment_body">';

    // print comment body or something?

    if (comment_has_reply($comment_id)) {
        foreach(comment_comments($comment_id) as $comment) {
            get_comments($comment->id);
        }
    }

    print '</div>';
}

To indent comments however, use css.

<style type="text/css">
.comment_body {
    margin-left:10px;
}
</style>

This way sub replies are indented more than the parent, and their subs are indented even more, and so on.