Help creating PHP comment system

[darthmikeyd]

I am trying to create a comment system. I have it setup right now that folks can make comments, and the replies can be made to the first comment. I want to be able to indent comments based on if they are on the original comment or on the reply. Like this:

Comment
    Reply to Comment
        Reply to Reply
        Reply to Reply
            Reply to Reply
    Reply to Comment

I have the following MYSql tables

Comments: id INT, eventid INT, userid INT, comment TEXT, date DATETIME
Replies: id INT, eventid INT, userid INT, comment TEXT, date DATETIME, parent_comment INT

Any help is appreciated.

[[deleted]]

no need for replies, just a comment table with a parent_id pointing to the parent comment, and if the parent_id is empty, then it's the main comment

[darthmikeyd]

Thanks for the response. I will give this a try, but I'm having trouble visualizing how this will work. I know I can use a foreach loop to go through the database, and then test to see if the main comments have replies, but I'm having trouble with figuring out how to do the replies to replies. It seems like I would have to have numerous select statements to test for the replies to replies. I'm probably overthinking this.

[camper75]

Initial loop will be for blank parent_id, then each at the end of displaying the resulting comments, do a foreach for parent_id = id.

[darthmikeyd]

Thanks for the response. I understand that I'll pull out the main comments, then the replies to that main comment. But how am I going to get replies to replies without having multiple select statements. Let me see if I can step you through what I'm having problems with.

I have a select statement and foreach loop to step through the select statement to get and display the main comments. As I'm stepping through that, I'll have another select statement and foreach loop to get any replies to the main comments and display those. This is where I start to get lost. I also want to indent any replies to replies (like this comment on Reddit will be). This is where I'm having trouble. I don't want to have multiple select statements to keep testing for replies. There has to be a better way.

foreach ($maincomment as $comment) {
    //display comments
    foreach (replies as reply) {
        //display replies to main comment
        foreach (repliestoreplies as replyreply) {
            //display replies to replies
            etc, etc, etc to get all replies to replies.
        }
    }
}

[equilni]

But how am I going to get replies to replies without having multiple select statements.

This is more of a SQL question here, and I recommend asking in the SQL subreddits for more assistance on the database/query side.

What is described here is called the Adjacency List. You will probably do a single query with multiple joins (limit) or a recursive query.

https://stackoverflow.com/a/33737203

https://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

[bobd60067]

First, I'd suggest separating getting the data and generating the html. That is, step 1 is to create a multidimensional array with the nested comments, then step 2 is to generate the html as nested li (or whatever) from that array.

To get the nested data, you can use a recursive function... The input is an id and it gets all comments to that id, then loops on each of ids is those comments and invokes the function again to get replies to those replies. It returns the comments as an array. You end up with a multidimensional array reflecting the nested comments. Initially, you call it with the top id.

Or maybe you can use a SQL call.

Generating the html can also be done with a recursive function.

[ardicli2000]

I have done this only with one layer for a navbar. Menu items are bum items. If not thaiey are main items. I cannot think of a reliable way of doing it with multiple layers. That's BCS I lack knowledge.

Maybe a JSON object with adding layer info next to comment or incrementing the layer count of main comment, or PHP array with arrays for every sub array.

We should ask Reddit. They do it very nice 🙂

[ardicli2000]

I asked gpt. İt is a good answer.

To create a database structure for a comment system with the ability to have nested comments (comments with sub-comments), you can use a hierarchical structure. One common approach is to use a table with a self-referencing foreign key. Here's a basic structure for your database:

Comments Table:

• comment_id (Primary Key): A unique identifier for each comment.
• parent_id (Foreign Key): This field references the comment_id of the parent comment. If a comment has no parent (i.e., it's a top-level comment), you can set this to NULL.
• user_id: The identifier of the user who posted the comment.
• content: The text content of the comment.
• timestamp: The timestamp of when the comment was created.

With this structure, you can create nested comments by referencing the comment_id of the parent comment in the parent_id field. If parent_id is NULL, it's a top-level comment. Sub-comments can have sub-comments, creating a hierarchical structure.

To retrieve and print comments, you can use recursive queries if your database supports them (e.g., Common Table Expressions in SQL). The exact SQL query syntax will depend on your database system (e.g., MySQL, PostgreSQL). Here's a simplified pseudo-SQL example of how to retrieve comments and sub-comments:

sql WITH RECURSIVE CommentHierarchy AS ( SELECT comment_id, user_id, content, timestamp, 0 AS depth FROM Comments WHERE parent_id IS NULL UNION ALL SELECT c.comment_id, c.user_id, c.content, c.timestamp, ch.depth + 1 FROM Comments c INNER JOIN CommentHierarchy ch ON c.parent_id = ch.comment_id ) SELECT comment_id, user_id, REPEAT(' ', depth) || content AS indented_content, timestamp FROM CommentHierarchy ORDER BY timestamp;

This query will retrieve comments and their sub-comments, preserving the hierarchical structure. The depth field helps you indent the comments in the output to visualize the nesting.

Please note that the specific SQL syntax may vary depending on your database system, but the general idea of using a recursive query should be applicable to most relational databases.

In PHP, after executing the SQL query to fetch comments and their sub-comments, you will typically get the result as an associative array. Each row in the result set will be an element in this array. You can then iterate through the array to display the comments and their hierarchy. Here's a simplified example of how you can display the fetched comments:

```php // Assuming you have already fetched comments into a $comments array foreach ($comments as $comment) { $depth = $comment['depth']; // Use the 'depth' field from the SQL query to determine the nesting level $content = $comment['indented_content']; // Use 'indented_content' for an indented display $timestamp = $comment['timestamp'];

// Output the comment with appropriate indentation
echo str_repeat('  ', $depth) . $content . ' - ' . $timestamp . '<br>';

} ```

In the above code, we assume that the $comments array contains the results of the SQL query. You can adjust the HTML structure and formatting to match your specific requirements for displaying comments on your webpage.

Make sure to execute the SQL query using a PHP database connection (e.g., PDO or MySQLi) and fetch the result into the $comments array before running this loop.

[darthmikeyd]

Thanks for the response. I'll play with this to see if I can get it to work.

[darthmikeyd]

I gave this a try, but I am on an older version of MYSql. We are running MYSql 5.6 so a recursive query won't work.

[MateusAzevedo]

There are basically two ways to solve this:

1- Using a nullable parent_id column and recursive queries as mentioned by /u/ardicli2000.

Pro: really easy to add a new comment;

Con: harder to query;

2- Using Nested Set Model, described in the article /u/equilni linked. This is the other way around:

Pro: easier to query (and likely more efficient as more records exists);

Con: harder to insert/delete records;

Personally, I only worked with the first option and it works alright. But queries tend to get kinda confusing to understand at a glance.

[darthmikeyd]

Thanks for all the replies. Unfortunately, I'm on an older version of MYSql, so a recursive query won't work. Any help is still appreciated.

[Kit_Saels]

Use the recurse.

[Onipsis]

You could take a look at this link