Create a reddit-like infinite levels comment reply system

Question

Imagine an comment system with an infinite reply structure. Like this:

Comment
  Reply to comment
    Reply to reply to comment
      Reply to reply to reply to comment
        etc....
  Reply to comment
Comment
  Reply to comment

Like reddit has:

I am trying to think of a structure inside PHP combined with mysql to do this. I thought of something like this:

$query = mysqli_query($link, "SELECT * FROM comments");    
while($comment_array = mysqli_fetch_assoc($query)){
   echo $comment_array['text'];

   $query_reply = mysqli_query($link, "SELECT * FROM comments WHERE reply_id='$comment_array[id]'");
   while($reply_array = mysqli_fetch_assoc($query_reply)){
      echo $reply_array['text'];

      $query_reply2 = mysqli_query($link, "SELECT * FROM comments WHERE reply_id='$reply_array[id]'");
      while($reply_array2 = mysqli_fetch_assoc($query_reply2)){
         echo $reply_array['text'];

         ...... etc.
      }
   }
}

But as you can see there is a problem in this structure. This structure is not infinite and the same bit of code must be repeated a lot of times.

Is there a way to do this more efficiently? Putting a loop somewhere? Making a function, like searchRepliesofComment();?

score 3 · Answer 1 · edited Sep 06 '16 at 12:59

There are a few ways to handle this without unbound recursion. A recursive function calling the DB repeatedly is not a good idea, even for reads. A few calls in a test environment may only take a few milliseconds each but even a little traffic can bring the DB to its knees when each request is firing off tons of DB hits.

First option you could have a post_id,parent_reply_id on all replies. The post_id would allow to select all replies that apply to a post but would have no hierarchy. Then you would build the reply tree in memory using parent_reply_id. Replies with a null parent_reply_id would represent top level replies. You could build this map easily with "SELECT * FROM replies WHERE post_id = ? ORDER BY parent_reply_id ASC". Ordering this way will make building the tree really straight forward.

You can also use the nested set model. Here is a great example. It does incur the cost of having to do a bulk (but relatively) light write operation on many replies when a new reply is added but the benefit is that you can build the tree with a single mysql query.

score 1 · Answer 2 · answered Dec 08 '15 at 21:44

1

You will want to build what's known as a recursive function

function getComments($link, $parent = null) 
{
   $sql = "SELECT * FROM comments";
   $sql .= $parent ? " WHERE reply_id=".(int)$parent : null;
   $query = mysqli_query($link, $sql); 

   $results = array();
   while ($result = mysqli_fetch_assoc($query)) {
     if ($children = getComments($result['id'])) {
       $result['children'] = $children;
     }
     $results[] = $result;
   }
   return $results;
}

function renderComments(array $comments)
{
    $output = '';
    foreach ($comments as $comment) {
      $output .= $comment['text'];
      if (isset($comment['children'])) {
        $output .= renderComments($comment['children']);
      }
    }
    return $output;
}

Now you can render your comments like:

renderComments(getComments($link));

answered Dec 08 '15 at 21:44

Ben Rowe

28,406
6
55
75

Recursion from php is not really efficient, the recursive part should be pushed to mysql in form of a stored procedure. – Shadow Dec 08 '15 at 21:49
@Shadow How do you mean? What is a stored procedure? Please post your answer, so it makes more sense to me. – Tom Groot Dec 09 '15 at 17:28
See the duplicate topic link on the top of the page. I flagged your topic with that initially. – Shadow Dec 09 '15 at 22:28

Create a reddit-like infinite levels comment reply system

2 Answers2