MySQL Table for comments

Question

This is a very amateur question (don't rate me down for it) but how would I create a column

for comments

containing the id of the post they are assigned to?

For example if I was to post a comment on post #48, how would I set that up in MySQL so the

comment shows up on post #48 and not all of the posts?

Thanks in advance:)

Answer 1

You don't create a column for comments, but a new table.

simply,

table Post
id,
content

table Comment
id,
content,
post_id

Where post_id is a reference to the id of the post.

Answer 2

Its a one to many relationship (one post can have many comments) so you'll be wanting a new table for it.

comments_tbl
 - comment_id  |   int(11) auto_increment
 - post_id     |   int(11) (FK to post table)
 - author_id   |   int(11) (FK to the user table OR author_name)
 - date        |   datetime
 - comment     |   text

And if you want to be able to flag and moderate comments you may wish to include something such as:

 - date_approved  |  datetime
 - flagged        |  int(1)

Your SQL then to display comments for a post would be like

mysql_query("SELECT comment_id, author_name, comment FROM comments_tbl WHERE post_id = '$postid' AND date_approved IS NOT NULL AND flagged = '0'");

To add a comment to the database:

mysql_query("INSERT INTO comments (post_id, author_id, date, comment) VALUES ('$postid', '$author_id', '$date', '$comment');