1

I have Post Table +----+-------+--------------+ | Id | Name | Message | +====+=======+==============+ | 1 | John | John's msg | | 2 | Marco | Marco's msg | | 3 | Ivan | Ivan's msg | +----+-------+--------------+

Comment table, PostId is foreign key

+----+-------+--------------+--------+
| Id | Name  | Comment      | PostId |
+====+=======+==============+========+
| 1  | John  | John's msg   | 2      |
| 2  |Joseph |Joseph's msg  | 2      |
| 3  | Ivan  | Ivan's msg   | 2      |
| 4  |Natalie|Natalie's msg | 1      |
+----+-------+--------------+--------+

Created closure table to keep comment hierarchy as described at: http://karwin.blogspot.ba/2010/03/rendering-trees-with-closure-tables.html

Closure table +----------+-------------+ | ancestor | descendant | +==========+=============+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 4 | 4 | +----------+-------------+

In order to create ancestor>descendant relation in PHP I use this query

`select c.* from comments c join closure t on (c.id = t.descendant ) where c.postid=2`

I get result with 1st 3 comments from comment table. But i want to display this on web page like this:

Post Id=2
    Comment id=1
        Comment id=2
        Comment id=3

I don't know how to tell PHP who is ancestor and who is descendant. This is my best try:

$sql = "select c.* from comments c join closure t on 
(c.id = t.descendant ) where c.postId=2";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "Name: " . $row["name"]."". "Message:" . $row["comment"].";
       }
} else {
    echo "No comments yet";
}

But it only shows all comments in line. Need a way to identify when comment is descendant.

while($row = $result->fetch_assoc()) {
if (ascendant){
    echo "ascendant comment"
}
else{
    echo "tab"."descendant comment"
}
}
Energizem
  • 211
  • 1
  • 5
  • 11
  • Aside from the fact that you did not faithfully produce a complete closure table above, this question was asked and answered more than four years before this post in https://stackoverflow.com/questions/14069674/sorting-a-subtree-in-a-closure-table-hierarchical-data-structure/14074310#14074310 – Jeff Apr 25 '18 at 16:13

0 Answers0