CODEWITHSUNDEEP
phpmysql
Energizem
Energizem

Reputation: 221

PHP MySQL Comment

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?

Upvotes: 0

Views: 91

Answers (2)

Murilo Azevedo
Murilo Azevedo

Reputation: 333

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;

Upvotes: 1

Rotimi
Rotimi

Reputation: 4825

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

Upvotes: 1

Related Questions