Path enumeration counter

My website has a comment system, and the comments are stored in a table that looks like the following:

post_id |  path  | total_replies
   1        1/          3
   2       1/2/         2
   3      1/2/3/        1
   4     1/2/3/4/       0

In words, post id 2 is a reply to post id 1, post id 3 is a reply to post id 2 which is a reply to post id 1, etc. It's literally just a path.

The total_replies column for post id 1 is 3 because there are 3 replies made to it (post id 2, 3, and 4), and the same logic is used for the other rows.

Because this is a comment system, more rows are added each time a new comment is made. What I'm trying to do is find a way to increment the total_replies column each time a new column is made.

So for example, if someone made a new reply (with post id 5) to post id 2, the table would look something like this:

post_id |  path  | total_replies
   1        1/          4 (this was incremented)
   2       1/2/         3 (this was incremented)
   3      1/2/3/        1
   4     1/2/3/4/       0
   5      1/2/5/        0

So, my question is, how would I do the incrementing each time a new reply is made?

EDIT: My current query.

$stmt = $cxn->prepare("UPDATE posts SET total_replies = total_replies+1 WHERE ? LIKE concat(?, '%') AND post_path != ?");
$stmt->bind_param('sss', $new_path, $path, $new_path);
$stmt->execute();

Solution

You can do what you want with a update and where clause:

update comments
    set total_replies = total_replies + 1
    where NEWPATH like concat(path, '%');

In this case, NEWPATH would be '1/2/5/'. It is unclear whether you have this in a variable or only as a new row in the table. This assumes that you have it as a variable.

EDIT:

If you do not want to update the original record:

update comments
    set total_replies = total_replies + 1
    where NEWPATH like concat(path, '%') and NEWPATH <> path;