Commenting system

[rondog]

I have created a site that has a lot of videos and at first where my videos live in the database I put a comments field. I realized though that that will not work since there will be more than 1 user putting a comment on a video.

 

How should I go about doing this? Ive made a comments table, but am unsure on what fields I need. Should each video have a table with comment? I dont see that as a practical solution since there will be about a 1000 tables then :-/ any suggestions?

[cmgmyr]

You only need 1 comments table. This will have the comment id, user id, video id, comment, and whatever else you want like date left or status

 

CREATE TABLE `comments` (
  `cid` int(11) NOT NULL auto_increment,
  `userid` int(11) NOT NULL default '0',
  `videoid` int(11) NOT NULL default '0',
  `message` text NOT NULL,
  `status` smallint(1) NOT NULL default '0',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`cid`)
);

[rondog]

so basically, a big table of comments and the way I pull them is to select comments where videoid = the video im on..Ahh gotcha! Once I read it like that I understand now. What is status for?

[cmgmyr]

It's something I use for the "owner" of the video/picture/whatever. If status is set to 0 then the owner didn't view it yet. Once they view the comment(s) then this is set to 1. It's up to you if you use this or not. If you don't you can just take it out of the query.

[rondog]

ah cool thank you. I dont think i will need that, but thats a good thing to know in the future