[Solved] Need help please,.I could not update the amount

[jemz]

I think I solved it.

I have problem in my stored proc and almost 2 weeks solving this problem, I want to update the amount of the parent if his children get paired.

For example if I inserted ‘9’ as (right)child of ‘7’

Then execute the SP
CALL update_amount(9)

Now,I want all the parent starting from 7 go up to his parent to parent etc… the amount will be updated,and will be given 500 if the level is 4 otherwise 250…but my sp will not update with the parent have only 1 child even he is the parent of those children get paired under his subtrees,example 4 and 6 they are the parent of 7 but there amount did not updated…How can I update there amount if they are the parent ?.

please help me thank you in advance.

CREATE TABLE IF NOT EXISTS `mytree` (
  `parentid` int(11) NOT NULL,
  `memberid` int(11) NOT NULL,
  `position` char(1) CHARACTER SET latin1 NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  UNIQUE KEY `idx_1` (`memberid`),
  UNIQUE KEY `idx_2` (`parentid`,`position`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `mytree` (`parentid`, `memberid`, `position`, `amount`) VALUES
    (0, 1, '', 1000.00),
    (6, 7, 'L', 0.00),
    (2, 5, 'R', 0.00),
    (1, 3, 'R', 0.00),
    (1, 2, 'L', 500.00),
    (4, 6, 'L', 0.00),
    (7, 8, 'L', 0.00),
    (2, 4, 'L', 0.00);

Here is my SP

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_amount`(IN `p_mem` INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    declare cnt INT;
    declare par_id INT;
    declare cntr INT;
    declare m_mem INT;
    declare chk_mem INT;
    declare chk_memberid INT;
    declare s_str VARCHAR(512);
    

    set cntr=0;
    set par_id = 1;
    set chk_mem = p_mem;
    set m_mem = p_mem;      

    
proc_label:BEGIN
    WHILE  par_id != 0 DO
      SELECT parentid,memberid INTO par_id,chk_memberid FROM mytree WHERE memberid=m_mem;
      SELECT count(*) INTO cnt FROM mytree WHERE parentid=par_id;        
         
      set cntr = cntr+1;
      set m_mem = par_id;
       
       IF cnt = 2 THEN
            IF cntr <= 4 THEN
              update mytree set amount = amount+500 
              where memberid=par_id;
            ELSE
              update mytree set amount = amount+250
              where memberid=par_id;
            END IF;
        ELSE
            IF chk_mem = chk_memberid THEN
               LEAVE proc_label;
            END IF;   
         END IF;           
    END WHILE;
  end;
END