PHP MySQL Comment

Question

I have MySQL table where user can post some content on website as follows:

CREATE TABLE `Posts` (
  `id` int(6) UNSIGNED NOT NULL,
  `name` varchar(30) NOT NULL,
  `post` varchar(8000) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
)

Now I want to create a table for user to comment on posts. I don't know right way to do this, lack of knowledge. Can I create new table like this

CREATE TABLE `Comments` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `comment` varchar(800) NOT NULL,
  `postId` varchar(8) NOT NULL,
)

and when user comments I can display that comment by connecting "id" from post table and "postID" from "comment" table.

Is this right way to do this, or is there better way to make comments on post table?

basically you can link them with a foreign key. When users make a comment, save the user id as a foreign key in the comment table, then to retrieve the comment made by a user, join the two tables in your query — Rotimi
– Rotimi, Commented Feb 14, 2017 at 14:59
Have a read of this article which explains the basics of foreign keys: sitepoint.com/mysql-foreign-keys-quicker-database-development — GreensterRox
– GreensterRox, Commented Feb 14, 2017 at 15:02

Murilo Azevedo · Accepted Answer · 2017-02-14 15:14:53Z

1

Basically, You can link with a foreign key like this.

Posts (
      id int(6) UNSIGNED NOT NULL,
      name varchar(30) NOT NULL,
      post varchar(8000) NOT NULL,
      date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

You can define a foreign key for user if you want(It's a good pratice).

CREATE TABLE Comments (
      id int(10) NOT NULL AUTO_INCREMENT,
      name varchar(30) NOT NULL,
      comment varchar(800) NOT NULL,
      postId int(11) NOT NULL,
      user_id int(11) DEFAULT NULL
);


ALTER TABLE Comments ADD CONSTRAINT id_fk_post
FOREIGN KEY(postId) REFERENCES Posts (id);


ALTER TABLE Comments ADD CONSTRAINT id_fk_user
FOREIGN KEY(user_id) REFERENCES Users (id);

Now, you can search all comments of post like this:

SELECT * FROM Comments where postId = 3;

answered Feb 14, 2017 at 15:14

Murilo Azevedo

3331 gold badge2 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rotimi · Accepted Answer · 2017-02-14 15:07:24Z

1

CREATE TABLE `Users` (
  `user_id` int(6) UNSIGNED NOT NULL,
  `name` varchar(30) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
)

CREATE TABLE `Comments` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `comment` varchar(800) NOT NULL,
  `postId` varchar(8) NOT NULL,
  `user_id_fk` int(6) NOT NULL
)

So as the user posts a comment, you save the user's id in the comment table together with the comment made.

To retrieve, try something like:

$query = 'SELECT Users.user_id, 
                 Users.name,
                 Comments.comment FROM User JOIN Comments ON Users.user_id= Comments.user_id_fk
                         ';

answered Feb 14, 2017 at 15:07

Rotimi

4,7804 gold badges21 silver badges28 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Comment

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related