Path enumeration counter

Question

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();

That doesn't seem like a very good way to store heirarchical data. Have a look at this: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database — Mike, Dec 14 '13 at 22:32

Gordon Linoff · Accepted Answer · 2013-12-14T23:35:47.510

2

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;

edited Dec 14 '13 at 23:35

answered Dec 14 '13 at 22:25

Gordon Linoff

1,242,037
58
646
786

However, the problem is that I'm incrementing the total_replies count for all of the paths related to the new one. So, for example, we just added `1/2/5/`. That would increment the total_replies of `1/2/` and `1/` because they are all replies under each other (`5` is a reply to `2` which is a reply to `1`). Would this mean that a loop would be needed to go through the numerical values `1/2/` and increment them like that? – Bagwell Dec 14 '13 at 22:28
2

The statement will update `'1/'`, `'1/2/'`, and `'1/2/5'` as one statement. To eliminate the last update, add ` and path <> NEWPATH` in the `where` clause. – Gordon Linoff Dec 14 '13 at 22:31
What do you mean by adding ` and <>? I'm unfamiliar with more complex queries. – Bagwell Dec 14 '13 at 22:33
I've actually also found another issue. Say perhaps we are replying to the following path: `1/2/` (and the id will be `4` for the new reply). This means that the new path will be `1/2/4/`. However, if the path `1/2/3/` exists in the database, your query will also increment the total replies of that row. Rather, it should only update `1/2/` and `1/2/4/`. – Bagwell Dec 15 '13 at 06:33
1

@Bagwell . . . The query will not increment `'1/2/3'` in that case. – Gordon Linoff Dec 15 '13 at 14:31
Ah, it was my misunderstanding with my variables. I got it working. Thanks a bunch! :) – Bagwell Dec 15 '13 at 17:39

Path enumeration counter

1 Answers1