querying in hierarchical data-structure in database and displaying results

Question

I thought to implement advanced commenting system in my website using PHP-MySql. I finally settled for a 3-level comment-reply system for this purpose. Well, for that purpose I came across this article to implement the data-structure of the SQL database.

I planned to use nested set mdoel for the feature I want to use. The structure of the comments are like this-

<ul>
    <li>Parent comment</li>
        <ul>
            <li>First reply of parent comment</li>
            <ul>
                <li>reply of the previous reply</li>
                   <ul>
                       <li>reply of the previous reply</li>
                       <li>another reply of the previous reply</li>
                   </ul>
                <li>another reply of the previous comment</li>
             </ul>
             <li>second reply of the parent comment</li>
        </ul>
</ul>

For this type of structure, I have been playing around with PHP to show the query detecting the parents and its child uniquely(for fetching user details associated with each comment) and produce the output in the manner if shown above. Do anyone have idea how to do it. Please help me out.

EDIT :

I have a seperate user table in SQL linked to the comment table as user.id=comment.id. So considering this, what would be the recommended approach to detect user activity for each comment? I mean, for ex- I want to fetch user name and email for a sub-comment of parent comment 2. Hoow could it be done?

yeah I am stuck to that. But for sake of time, it can be as many levels as it can display. I am only interested in the code. — ashutosh
– ashutosh, Commented Aug 19, 2012 at 19:00

Barmar · Accepted Answer · 2012-08-20 00:10:55Z

1

Use the query from "Finding the Depth of the Nodes", and this PHP code will create the nested lists.

$cur_depth = -1;
while ($row = mysqli_fetch_assoc($query)) { // Loop through results of query
  if ($row['depth'] > $cur_depth) {
    echo "<ul>\n";
    $cur_depth = $row['depth'];
  }
  else while ($cur_depth > $row['depth']) {
    echo "</ul>\n";
    $cur_depth--;
  }
  echo "<li>" . $row['comment'] . "</li>\n";
}
while ($cur_depth > -1) {
  echo "</ul>\n";
  $cur_depth--;
}

edited Aug 20, 2012 at 0:10

answered Aug 19, 2012 at 19:03

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ashutosh Over a year ago

this doesn't make the comment specific to any user. I said I also do have to fetch user details for each comments

Barmar Over a year ago

Just add additional joins to the query to get information from the user table, etc. Then replace $row['comment'] with whatever formatting you want to do for all the fields.

ashutosh Over a year ago

could you please edit your answer commenting out the purpose of variables and where they came from? i mean i can guess, but for sake of knowledge base, could you please?

Barmar Over a year ago

I just edited my answer to show that it's looping through the results of a query -- the for-loop was left over from my testing with manually-created data. The variables just come from the result of the query on the web site you referenced, except that instead of its name column I used comment. But you can use any columns you want, as well as additional columns that you get from other tables that you join with. This is just a basic outline, you need to fill in all your application-specific details.

ashutosh Over a year ago

well, then I think my root problem is over. Now only algorithm left is to decide when the comment-reply system should be stopped for a n-level commenting. You solved my 80% problem. thanks

Collectives™ on Stack Overflow

querying in hierarchical data-structure in database and displaying results

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related