How to find the level and number of childs in binary tree

See more:

PHP

MySQL

CodeIgniter

Dear Friends,

I have to develop the MLM project, I want to calculate
1. Number of children from each node.
2. find the level of node.

1
/ \
2 3
/ \ / \
4 5 6 7

For Eg. Tot. no. of child for '1' is 6
Tot. no. of child for '2' is 2
Tot. no. of child for '4' is 0
...........

What I have tried:

I have db table like this:

id parent_id Leg
--------------------
1 0
2 1 L
3 1 R
4 2 L
5 2 R
6 3 L
7 3 R

I tried this function it makes error:
CREATE FUNCTION get_counts(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE _id INT;
DECLARE _parent INT;
DECLARE _next INT;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

SET _parent = @id;
SET _id = -1;

IF @id IS NULL THEN
RETURN NULL;
END IF;

LOOP
SELECT MIN(id)
INTO @id
FROM my_registration
WHERE sponser_id = _parent
AND id > _id;
IF @id IS NOT NULL OR _parent = @start_with THEN
SET @level = @level + 1;
RETURN @id;
END IF;
SET @level := @level - 1;
SELECT id, sponser_id
INTO _id, _parent
FROM my_registration
WHERE id = _parent;
END LOOP;
END

ERROR is: MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5