CODEWITHSUNDEEP
mysql
Ayman Hussein
Ayman Hussein

Reputation: 3857

get child level in mysql query

I have table has id and perent as the following:

id   parent
12      0
21      12
34      12
47      21
57      21
66      21
77      34
88      47

I want to write query take id and return which level this id:

for example

1 => level 1
2 and 3 => level 2
4 => level 3
... and so on.

what i tried:

SELECT cc1.id, count(cc1.id) c FROM community_comments cc1
inner join community_comments cc2 on cc1.id = cc2.parent 
AND cc1.parent = 324 group by cc1.parent

this return how many level under 324 level and this not what i want

Upvotes: 3

Views: 197

Answers (1)

Ayman Hussein
Ayman Hussein

Reputation: 3857

I am solving it using PHP code

function get_comment_level($comment_id){

        $query = "select parent from community_comments where id = $comment_id";

        $result = $this->conn->db_query($query);

        $level = 1;
        $parent = 0;

        while ($row = $this->conn->db_fetch_object($result)){
            $parent = $row->parent;
        }

        if ($parent != 0) {
            $level = $this->get_comment_level($parent);
            $level++;
        }

        return $level;
    }

Upvotes: 2

Related Questions