CODEWITHSUNDEEP
phpmysql
Jamal Stanfield
Jamal Stanfield

Reputation: 51

MySQL Table for comments

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:)

Upvotes: 3

Views: 899

Answers (5)

lethalMango
lethalMango

Reputation: 4491

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');

Upvotes: 2

a1ex07
a1ex07

Reputation: 37364

You need something like

CREATE TABLE `comments` (id int unsigned not null auto_increment PRIMARY KEY,
post_id int unsigned not NULL,
// other fields, post_date, post_text, etc
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE ON UPDATE CASCADE)

Note : FOREIGN KEY are supported by INNODB engine, so if your table is MyISAM, you don't need it. post_id must have the same type as primary key in posts - I assumed post.id is unsigned integer.

Upvotes: 0

SamT
SamT

Reputation: 10610

If you have a table for your posts, you will need a new table for your comments. To associate comments with posts, just make a post_id column in your comments table.

comments
- comment_id int(8) Auto_Increment 
- post_id int(8)
- comment_author varchar(255)
- comment_content text()

post_id should be the ID of the post that you are associating the comment in, where each comment gets it's own row in this table. If your authors can be associated by an ID that is better, just change the varchar(255) to int(8) or something that fits in with the rest of your design.

Upvotes: 0

Erveron
Erveron

Reputation: 1928

First you need to do in PHP code is to store in a variable ID of the post the comment is assigned to (for example $postId. Then you can insert comment into new table somewhow like this (I'm not writing table structure, hope you will see it from the query):

mysql_query("INSERT INTO comments (id_post, text) VALUES ('".$postId."', 'Text of the comment'");

When you want to select it, you will use this query:

mysql_query("SELECT text FROM comments WHERE id_post = '".$postId."'");

By the way, the id_post column is called foreign key ane there should be defined index for it.

Upvotes: 0

joakimdahlstrom
joakimdahlstrom

Reputation: 1595

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.

Upvotes: 6

Related Questions