I'm hoping you experts can help me think through the best approach for something I am working on.
On the site, we have 3 sections that contain various details about specific phases (Phase 1, Phase 2, Phase 3) of a project. Users will have the ability to post comments in each of the sections. They will also be able to make comments on previous comments, but only 1 nested level deep.
So:
===================
Project 1
Phase 1
Phase 2Comment 1Comment 1a
Comment 2
Phase 3
Project 2
Phase 1Comment 1Comment 1a
Comment 1b
Comment 2Comment 2a
Phase 2
Phase 3
===================
Etc.
To handle this, I am thinking that my table will need to contain the following columns:
===============
| comments.tbl |
===============
| comment_ID |
| project_ID |
| phase_ID |
| top_ID |
| user_ID |
| comment_ts |
===============
WHERE
comment_ID is the primary key
project_ID ties to the project ID stored in the projects.tbl
phase_ID indicates which phase section this comment belongs
top_ID if not null references the comment_ID in this table that this comment is in reference to
user_ID ties to the user ID stored in the users.tbl for the poster
comment_ts stores the timestamp when the comment was posted
Assuming that is the correct structure (which I'm sure you'll tell me if it's not), how would I best query this table in PHP to output my comments in the format shown above. I know I would put the project_ID and phase_ID in the WHERE clause to narrow it down that far. But how would I show the comments nested appropriately:
===============
Comment 1
Comment 1a
Comment 1b
Comment 2
Comment 2a
===============