How to find position from recursive hierarchy, Parent-Child Relationship mysql

Question

I am very tired getting tree based result, which id on which position. But not reached, also saw many answer like select and select

Here is my table

CREATE TABLE `memberinfo` (
  `id` int(11) NOT NULL auto_increment,
  `parentid` int(11) NOT NULL default '0',
  `name` varchar(250) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


id  parentid name
1     0      abc1
2     1      abc2
3     2      abc3
4     2      abc4
5     0      abc5
6     3      abc6

I want result position like:

id position
1      1
2      1-1
3      1-1-1
4      1-1-2
5      2
6      1-1-1-1

Thank you for help in advance.

I try in php recursive but it too slow and at last get fatal error(Excution time out)

function getPosition($listparentid,$listid){

    $db = new dbfunction();
    $posNumber = '';
    do{
        $listresult = $db->rootPostionSearch($listparentid,$listid);
        $rowcount=mysqli_num_rows($listresult);
        if($rowcount > '0'){
            if ($listrow = mysqli_fetch_assoc($listresult)) {

                //switch to parent id
                $listid = $listrow['parentid'];

                //get next parent id
                $presult = $db->getRootParentInfo($listid);
                $pcount=mysqli_num_rows($presult);
                if($pcount > '0'){
                    if ($plistrow = mysqli_fetch_assoc($presult)) {
                        $listparentid = $plistrow['parentid'];
                    }
                }

                //get position
                $posNumber = $listrow['position']."-".$posNumber;
            }
        }

    }while($listparentid != '0');


    // get parent position number
    $listresult = $db->rootPostionSearch($listparentid,$listid);
    $rowcount=mysqli_num_rows($listresult);
    if($rowcount > '0'){
        if ($listrow = mysqli_fetch_assoc($listresult)) {
            //get position
            $posNumber = $listrow['position']."-".$posNumber;
        }
    }

    return $posNumber;
}


public function getRootParentInfo($id){
        $sql = "select parentid from memberinfo where id = $id";
        return $this->query($sql);
}

public function rootPostionSearch($rootpos,$pos){
        $sql = "SELECT x.id, x.parentid, x.position, x.name
            FROM 
            (
                SELECT t.id, t.parentid, t.name,@rownum := @rownum + 1 AS position
                FROM memberinfo t
                JOIN (SELECT @rownum := 0) r 
                where t.parentid = '$rootpos' order by id
            ) x where id = '$pos'";

        return $this->query($sql);
}

Level query :

SELECT x.id, x.parentid, x.position, x.name
        FROM 
        (
            SELECT t.id, t.parentid, t.name,@rownum := @rownum + 1 AS position
            FROM memberinfo t
            JOIN (SELECT @rownum := 0) r 
            where t.parentid = '0' order by id
        ) x

does this help? http://explainextended.com/2009/03/18/hierarchical-queries-in-mysql-adding-level/ — Trent, Dec 16 '15 at 12:04
you aren't going to get much help here if you show no real attempt to solve the problem yourself. — Trent, Dec 17 '15 at 12:58
@Trent I already try recursion in php, but getting execution time out fatal error. please see updates — flex, Dec 21 '15 at 04:55

score 0 · Accepted Answer · edited Apr 13 '17 at 12:42

I show your problem related to @RolandoMySQLDBA answer and I done small change, parentid to parent_id into memberinfo table. My it helps you :

DELIMITER $$
DROP FUNCTION IF EXISTS `GetAncestry` $$
CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;
    DECLARE cid INT;
    DECLARE label VARCHAR(24);


    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    SET cid = 0;
    SET label = '';
    WHILE ch > 0 DO
    SET cid = ch;
        SELECT IFNULL(parent_id,-1) INTO ch FROM
        (SELECT * FROM pctable WHERE id = ch) A;

    SELECT pos INTO label FROM
        (

SELECT x.id, x.parent_id, x.pos
FROM 
(
SELECT t.id, t.parent_id, @rownum := @rownum + 1 AS pos
FROM pctable t
JOIN (SELECT @rownum := 0) r 
where t.parent_id = ch order by id
) x where x.id = cid

    ) P;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,label);
            SET cm = '-';
    else
            SET rv = CONCAT(rv,cm,label);
        END IF;
    END WHILE;
    RETURN rv;
END $$
DELIMITER ;

And call here like :

select id,GetAncestry(id) from memberinfo

and answer gets like :

id position
1      1
2      1-1
3      1-1-1
4      1-1-2
5      2
6      1-1-1-1

How to find position from recursive hierarchy, Parent-Child Relationship mysql

1 Answers1