How can i get/query/list sub/nested comments from comment table?

[colap]

Hi,

 

Comment table:

+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id         | int(11)      | YES  |     | NULL    |                |
| post_id         | int(11)      | YES  |     | NULL    |                |
| comment_content | varchar(255) | YES  |     | NULL    |                |
| created         | datetime     | YES  |     | NULL    |                |
| modified        | datetime     | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

Normally we get the list of comments associated with a post like this:

select * from comments where post_id=<anypostid>

Then we can do in this way:

$sql_allcomments="select *,(select username from users where id=user_id) as username from comments where post_id=$post_id order by created ASC";
$stmt_comments=$dbh->prepare($sql_allcomments);
$stmt_comments->execute();
$result_comments=$stmt_comments->fetchAll();
?>
<div id="id_div_comment_content" class="cl_div_width_500px">
    <?php foreach ($result_comments as $value) { ?>
        <div class="cl_div_one_comment cl_div_border_solid cl_div_margin_bottom1px">
            <div><a href="/domain/user.php?id=<?php echo $value['user_id']; ?>"><?php echo $value['username']; ?></a>
            <?php echo ' at ' . $value['created']; ?>
            </div>            
            <div><?php echo $value['comment_content']; ?></div>
        </div>
    <?php } ?>
    
</div>

This is the new comment table with a parent_comment_id column:

I'm trying to make a commenting system where someone can reply to a comment too.

+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id           | int(11)      | YES  |     | NULL    |                |
| post_id           | int(11)      | YES  |     | NULL    |                |
| comment_content   | varchar(255) | YES  |     | NULL    |                |
| parent_comment_id | int(11)      | YES  |     | NULL    |                |
| created           | datetime     | YES  |     | NULL    |                |
| modified          | datetime     | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

In this case, how can i list/get/query sub/nested comments of a comment?

This is an example of nested commenting system. There are many comments under comments.

https://www.reddit.com/r/programming/comments/z9sm8/reddits_database_has_only_two_tables/

 

How can i do something like this?

 

Any answer will be highly appreciated.

Thanks in advance.

Edited July 7, 2016 by php-coder

[Barand]

 

$sql_allcomments="select *,(select username from users where id=user_id) as username from comments where post_id=$post_id order by created ASC";

 

Use a JOIN, so you only execute a single query, and not the subquery. If you have 1000 comments your query will generate 1000 queries. Not good for server performance.

 

You will need a recursive function to process the nested comments. Read the data into an an array (indexed on parent comment). Don't call queries recursively.

[kicken]

Have a look at this article for an example of how to build a tree structure out of a liner array:

 

Building Tree Structures In Php Using References

[colap]

Use a JOIN, so you only execute a single query, and not the subquery. If you have 1000 comments your query will generate 1000 queries. Not good for server performance.

 

You will need a recursive function to process the nested comments. Read the data into an an array (indexed on parent comment). Don't call queries recursively.

 

Are you suggesting to avoid this following query?

$sql_allcomments="select *,(select username from users where id=user_id) as username from comments where post_id=$post_id order by created ASC";

Will this query run 1000 times if there are 1000 comments?

[Barand]

Will this query run 1000 times if there are 1000 comments?

The subquery will need to find the username for each of the 1000 records.

 

Use

select c.*,
    u.username 
from comments c
    inner join users u ON c.user_id = u.id
where post_id=$post_id 
order by created ASC

A couple more observations:

• don't use SELECT *, specify the columns required
• use a prepared query instead of putting user-submitted data directly into the query

[colap]

 

A couple more observations:

• don't use SELECT *, specify the columns required
• use a prepared query instead of putting user-submitted data directly into the query

 

What's prepared query? What did you mean by 'putting user-submitted data directly into the query'?

Can you post example?

Edited July 7, 2016 by php-coder

[Barand]

see this reply from earlier today

 

http://forums.phpfreaks.com/topic/301437-form-not-submitting-if-text-field-is-too-longlarge-php-mysql/?do=findComment&comment=1534295

[Barand]

try something like this

$db = new mysqli(HOST,USERNAME,PASSWORD,'test');

$sql = "SELECT comment_id
          , username
          , post_id
          , content
          , IFNULL(parent_comment_id,0)
          , created
        FROM comment c
            INNER JOIN user u ON c.user_id = u.id
        ORDER BY post_id, created";
$comments = [];
$res = $db->query($sql);
while (list($cid,$user,$pid,$content,$parent,$created) = $res->fetch_row()) {
    $comments[$pid][$parent][$cid] = [  'content' => $content,
                                        'user'    => $user,
                                        'date'    => $created
                                     ];
}


//
// recursive print function
//
function printReplies(&$comments, $parent, $level)
{
    if (!isset($comments[$parent])) return;
    
    foreach ($comments[$parent] as $cid => $comdata) {
        $dval = date('F jS Y g:ia', strtotime($comdata['date']));
        // print comment
        echo "<div class='comdiv lev$level'>
                <b>{$comdata['user']}</b><br>$dval<br>
                {$comdata['content']}
            </div>\n";
        // print replies to the comment
        printReplies($comments, $cid, $level+1);
    }
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Sample sub-comments</title>
<style type='text/css'>
.comdiv {
    border: 1px solid gray;
    width: 400px;
    margin-top: 5px;
    padding: 10px;
}
.lev0 {
    margin-left: 50px;
    background-color: #cfc;
}
.lev1 {
    margin-left: 100px;
    background-color: #ccf;
}
.lev2 {
    margin-left: 150px;
    background-color: #ffc;
}
</style>
</head>
<body>
    <?php
        foreach ($comments as $post => $comms) {
            echo "<h3>Post $post</h3>";
            printReplies($comms, 0, 0);
        }
    ?>
</body>
</html>

results attached