0

I'm basically trying to code a simple threaded comments system where users can comment on other user's comment. It'll allow only one level of comments.

The comments table in the database is something like: - id - text - timestamp - parent_id (can be NULL)

My question is how should I query the comments and their children (comments)? I'm just not sure how they would be places in the array, and then how to loop and output them properly.

Your help is much appreciated =)

KeyStroke
  • 1,455
  • 5
  • 19
  • 24

2 Answers2

2

If its only one level deep, you could get all comments and munge them into the structure you want. You could do something like this :

function get_comments()
{
    $sql = 'select * from comments order by timestamp';
    $result = $this->db->query($sql)->result();

    $comments = array();
    foreach ($result as $row)
    {
        if ($row->parent_id)
        {
            $comments[$row->parent_id]->children[] = $row;
        }
        else
        {
            $comments[$row->id] = $row;
            $comments[$row->id]->children = array();
        }
    }

    return array_values($comments);
}
Stephen Curran
  • 7,433
  • 2
  • 31
  • 22
1

Here is a fairly detailed explanation on how to handle this:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

profitphp
  • 8,104
  • 2
  • 28
  • 21
  • I've read it but I'm afraid I still don't know how to do it. Correct me if I'm wrong, but the link explains how to fetch just one child in each level, not all of them, right? – KeyStroke Dec 10 '10 at 17:23