PHP: Quick threaded comments question

Question

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 =)

Answer 1

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