mysql structure for posts and comments
I've read several tutorials, documentations about mysql, db structures and also I'm using it via php for weeks. Now I get a problem, I don't know how to form/organize/create my db structure for posts and comments. I've already read some posts about this (here on stackoverflow), but I didn't find anything useful. I understand that I need to have 2 tables for posts and comments, and when I need to print them on the page with a foreign key (or ID) I "merge" them (only on the page, not with SQL). When a person is viewing the page, he is seeing the post and comments normally, but in the "background" everything is stored in 2 tables.
Am I needing to add a new column every time when somebody is adding a new comment or a reply?
If my question is true, that means if in a post are 100+ comments, that means I need to ALTER the TABLE every single time? That means if post "A" has 3 comments and post "B" has 150 comments, my table "comments" will have 100+ columns?
E.g:
Posts | column1 | column2 | ... | columnN
A | bla1 | bla2 | bla3 | - empty | - empty | ... | - empty - |
B | bla1 | bal2 | bla3 | bla4 | bla5 | bla6 | ... | bla100 |
Answer
At a basic level, you would have a table for each type of "thing" in your application. In this case, a table for Posts and a table for Comments. Something as simple as this:
Post
--------
Id
Content
User
DatePosted
Comment
--------
Id
PostId
Content
User
DatePosted
This would create what's called a one-to-many (or zero-to-many, actually) relationship between Posts and Comments, whereby each Post can have zero or more associated Pomments but each Comment can be associated with only one Post.
In your code (which is a whole other subject entirely), to display a Post and its associated Comments there are a couple of things you could do. Assuming you have, as input, the Id of the Post you want, you can get that Post and its Comments:
SELECT `Content`, `User`, `DatePosted` FROM `Post` WHERE `Id` = ?Id
SELECT `Id`, `Content`, `User`, `DatePosted` FROM `Comment` WHERE `PostId` = ?Id
What you do with that resulting data is up to you and how you want to use it in your application. It would come back as two table results, the former of which has one record (if the Post exists) and the latter of which has zero or more records. Naturally, you'll want to check that things exist before trying to use them, etc. (So if the first query returns no results, don't try to continue to display the Post. Just show a default response or an error.)